Transpose Vertical Data Horizontally with Date break

[calinini]

Hello!

I have a set of data with the dates and times in a column. However I need the dates to remain vertical and the times to be horizontal.

Example:

NAME	ID	DATE	TIME
ABC	123456	5/8/2020	6:00A
ABC	123456	5/8/2020	11:00A
ABC	123456	5/8/2020	12:00P
ABC	123456	5/8/2020	4:00P
ABC	123456	5/9/2020	5:58A
ABC	123456	5/9/2020	4:00P
BCD	234567	5/8/2020	10:00A
BCD	234567	5/8/2020	2:00P
BCD	234567	5/8/2020	3:00P
BCD	234567	5/8/2020	8:00P

The desired output would be:

NAME	ID	DATE	TIME1	TIME2	TIME3	TIME4	TIME5
ABC	123456	5/8/2020	6:00A	11:00A	12:00P	4:00P
ABC	123456	5/9/2020	5:58A	4:00P
BCD	234567	5/8/2020	10:00A	2:00P	3:00P	8:00P

While I could transpose all manually, I have over 35000 rows of data.

Some days, a person might have 2 punches, on another 8. This is why a simply transpose doesn't work, nor a pivot table.

HELP PLEASE. TIA.

 

[alansidman]

Will this presentation work for you. If yes, advise and I will share solution.

Book4
	A	B	C	D	E	F	G	H	I
1	NAME	Custom.ID	Custom.DATE	1	2	3	4	5	6
2	ABC	123456	5/8/2020	6:00A	11:00A	12:00P	4:00P
3	ABC	123456	5/9/2020					5:58A	4:00P
4	BCD	234567	5/8/2020	10:00A	2:00P	3:00P	8:00P
Sheet2

 

[calinini]

Will this presentation work for you. If yes, advise and I will share solution.

Book4
	A	B	C	D	E	F	G	H	I
1	NAME	Custom.ID	Custom.DATE	1	2	3	4	5	6
2	ABC	123456	5/8/2020	6:00A	11:00A	12:00P	4:00P
3	ABC	123456	5/9/2020					5:58A	4:00P
4	BCD	234567	5/8/2020	10:00A	2:00P	3:00P	8:00P
Sheet2

Possibly. Looking at this though would every date have to be in a different col? I see that 5/9/2020 starts on COLH. I have approx 400 Names and the date span is 5/8/2020 - 6/4/2020. Ultimately, yes I could filter and delete cells and shift over if need be.

 

[alansidman]

With Power Query, here is the Mcode for the steps taken.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NAME", type text}, {"ID", Int64.Type}, {"DATE", type date}, {"TIME", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"NAME"}, {{"Data", each _, type table [NAME=text, ID=number, DATE=date, TIME=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "Index", 1, 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"ID", "DATE", "TIME", "Index"}, {"Custom.ID", "Custom.DATE", "Custom.TIME", "Custom.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Data"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.TIME")
in
    #"Pivoted Column"