Help, merging data rows with unique values.

[SiEn]

Good Morning All,

I am after some help please, I have created multiple spreadsheets to automate some workers hours on site..
I have various sheets using power query to obtain the final information unfortunately due to the way the data is written it contains some duplicated (of sorts) lines and i would really like to be able to merge this data.. I have taken a small snippet from my sheet which hopefully shows the issue i have..

Basically any row of data that has the same worker name and job number (first 2 columns) i would like to be combined into 1 row (this could be more than 2 rows), they will have data that is unique to that row (hrs worked) that also need to be combined or summed together..

(i.e in the snippet below the 2 lines would become one with the hours on 1 line and any that requiring summing together would be..)

Is this possible? is it easy?
Any help appreciated..
Many Thanks Simon

 

[alansidman]

Cannot manipulate data in a picture. If you can, repost some data (8-10) records using XL2BB. I believe that this can be resolved using PQ and using the Group By function to sum the various columns.

 

[SiEn]

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Worker Name	Job Number	Employer	Trade	Basic (Mon)	OT A (Mon)	OT Single (Mon)	Nights (Mon)	OT Nights (Mon)	Sick (Mon)	Unpaid (Mon)	Holiday (Mon)	Basic (Tue)	OTA (Tue)	OT Single (Tue)	Nights (Tue)	OT Nights (Tue)	Sick (Tue)	Unpaid (Tue)	Holiday (Tue)
2	Worker 1	123456			7.5	2.5							3.75
3	Worker 1	123456											3.75
4	Worker 2	234567			7.5								5
5	Worker 2	234567											2.5
6	Worker 3	345678			7.5	1							7
7	Worker 3	345678				1.5							0.5
8	Worker 4	456789						4	2.5							7.5	1.5
9	Worker 4	456789						3.5									1
Sheet1

 

[SiEn]

took a while but think i have it above!

 

[alansidman]

With Power Query, 1--Unpivot your data and then re-pivot it to summarize

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Worker Name", "Job Number", "Employer", "Trade"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value", List.Sum)
in
    #"Pivoted Column"

Worker Name	Job Number	Employer	Trade	Basic (Mon)	OT A (Mon)	Basic (Tue)	Nights (Mon)	OT Nights (Mon)	Nights (Tue)	OT Nights (Tue)
Worker 1	123456			7.5	2.5	7.5
Worker 2	234567			7.5		7.5
Worker 3	345678			7.5	2.5	7.5
Worker 4	456789						7.5	2.5	7.5	2.5

 

[SiEn]

wow that's great is it possible to return all the original columns incl blanks?

 

[alansidman]

That will take a different approach. Using Power Query, Group By the first four columns and then sum each of the remaining columns. I will do this for the first three and you can then see how and be able to do for all columns.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Worker Name", "Job Number", "Employer", "Trade"}, {{"Total Basic (Mon)", each List.Sum([#"Basic (Mon)"]), type nullable number}, {"Total OTA (Mon)", each List.Sum([#"OT A (Mon)"]), type nullable number}, {"Total OT Single (Mon)", each List.Sum([#"OT Single (Mon)"]), type any}})
in
    #"Grouped Rows"