looking for solution in Power Query

[srehman]

Below is the table for cycle counts 1st , 2nd & 3rd according to time and date , but i want everything in one line , i need solution in power query please.

test2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	List ID	Pack Config	Location	Location Zone	Sub Zone	Hazmat ID	Batch ID	Count Date	Count Time	Expected	Counted	Variance
2	619567	12X24X34	AC03900A	50PIC01	LSS-SHOE			21/05/2020 0:00	12:10:41 PM	3	2	-1
3	619570	12X24X34	AC03900A	50PIC01	LSS-SHOE			21/05/2020 0:00	12:15:10 PM	4	6	2
4	619569	12X24X34	AC03900A	50PIC01	LSS-SHOE			21/05/2020 0:00	12:12:54 PM	2	4	2
Sheet1

test2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
7									Count1					Count 2					Count 3
8	List ID	Pack Config	Location	Location Zone	Sub Zone	Hazmat ID	Batch ID	Count Date	Count Time	Expected	Counted	Variance	Count Date	Count Time	Expected	Counted	Variance	Count Date	Count Time	Expected	Counted	Variance
9	619567	12X24X34	AC03900A	50PIC01	LSS-SHOE			21/05/2020 0:00	12:10:41 PM	3	2	-1	21/05/2020 0:00	12:15:10 PM	4	6	2	21/05/2020 0:00	12:12:54 PM	2	4	2
Sheet1

 

[GraH]

Hello,

One way is grouping on Pack Config, ...., Date. Using all rows as aggregate. I name this column often simply All.
Then use custom columns with the following formula structure: Table.Column([All], "Count Date") {0}, and repeat for Count Time, Expected, Counted and Variance.
To have 2nd and third from each list, replace {0} by {1}, {2}.
Formula for first List ID follows the same logic or you add a Min aggregate and select the column List ID.

Another faster way is repeating the same grouping, then using a Custom Column for each record: = [All] {0}, [All] {1}, [All] {2}. Finally extracting only those fields from the record you need in the final result.

Both are assuming there are always 3 records.

 

[srehman]

Thanks for your support & time at this moment i cant understand your solution could my level is very low. I am trying .....
is there any one can you explain in more simple way if you can .......... sorry Even i tried not achieving.

 

[GraH]

I thought to be rather explicit already. So here is the code. I hope this makes sense to you. If needed just ask me to explain a specific step in further detail. I can try that.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Pack Config", "Location", "Location Zone", "Sub Zone", "Hazmat ID", "Batch ID"}, {{"ListID", each List.Min([List ID]), type number}, {"All", each _, type table [List ID=number, Pack Config=text, Location=text, Location Zone=text, Sub Zone=text, Hazmat ID=none, Batch ID=none, Count Date=datetime, Count Time=text, Expected=number, Counted=number, Variance=number]}}),
    ListIDAsFirstCol = Table.ReorderColumns(#"Grouped Rows",{"ListID", "Pack Config", "Location", "Location Zone", "Sub Zone", "Hazmat ID", "Batch ID", "All"}),
    GetFirst = Table.AddColumn(ListIDAsFirstCol, "First", each [All] {0}),
    GetSecond = Table.AddColumn(GetFirst, "Second", each [All]{1}),
    GetThird = Table.AddColumn(GetSecond, "Third", each [All]{2}),
    #"Expanded First" = Table.ExpandRecordColumn(GetThird, "First", {"Count Date", "Count Time", "Expected", "Counted", "Variance"}, {"Count Date.1", "Count Time", "Expected", "Counted", "Variance"}),
    #"Expanded Second" = Table.ExpandRecordColumn(#"Expanded First", "Second", {"Count Date", "Count Time", "Expected", "Counted", "Variance"}, {"Count Date.2", "Count Time.1", "Expected.1", "Counted.1", "Variance.1"}),
    #"Expanded Third" = Table.ExpandRecordColumn(#"Expanded Second", "Third", {"Count Date", "Count Time", "Expected", "Counted", "Variance"}, {"Count Date", "Count Time.2", "Expected.2", "Counted.2", "Variance.2"})
in
    #"Expanded Third"

On the UI:

Getting Records

Expanding

 

[srehman]

Gr8 Thanks a lot its perfectly work now i have to implement same scenerio around 10000 lines and more column.

Now concept is more clear.

You are Champ.

 

[srehman]

i just add few more lines i believe this query will work without any changes but i ma facing errors bc i have to implement this solutions around 10000 lines with different locations and List Ids

 

[GraH]

Gr8 Thanks a lot its perfectly work now i have to implement same scenerio around 10000 lines and more column.

Now concept is more clear.

You are Champ.

You're welcome.

i just add few more lines i believe this query will work without any changes but i ma facing errors bc i have to implement this solutions around 10000 lines with different locations and List Ids

It all depends on how you need the grouping to appear, since you only had 1 scenario in your sample data, I could not foresee any potential hiccups. So try the solution on the full data set and see if you have code and or logical errors.