how to merge rows after pivot and group by...

[joe1250]

Hi all, so I'm trying to figure out how to merge rows after I've pivoted and grouped by... the example below is probably clearer than trying to explain...

Original:

File #	File Responsibility	Assigned To
0001	Expert	Jim
0001	Admin	Diane
0001	Reviewer	Frank
0001	Research	Sharon

Current Result After Pivot and Group By:

File #	Expert	Admin	Reviewer	Research
0001	Jim	null	null	null
0001	null	Diane	null	null
0001	null	null	Frank	null
0001	null	null	null	Sharon

Desired Outcome:

File #	Expert	Admin	Reviewer	Research
0001	Jim	Diane	Frank	Sharon

I can't seem to figure this out and google hasn't been much help so I'm hoping someone on here has worked this out before?

Thanks!

 

[Bill Szysz]

Try this code below ( "Table 1" is your original table and not after pivoting and grouping)

let    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"File #", Int64.Type}, {"File Responsibility", type text}, {"Assigned To", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"File #"}, {{"tbl", each Table.PromoteHeaders(Table.Transpose(_[[File Responsibility],[Assigned To]])), type table}}),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Grouped Rows", "tbl", {"Expert", "Admin", "Reviewer", "Research"})
in
    #"Expanded {0}"

 

[joe1250]

Thanks Bill... my example above was a simplified version of actual data, but i was able (I'm pretty sure...) to figure out how to modify and where to insert your suggestion into my existing query to allow it to function as you would have intended... however it doesn't quite seem to work.

It does create the appropriate columns, but all of the column values are null...

When i investigate by expanding one of the tables for a file # after the Grouped Rows step, but before the Expand step, the column values are the headers and the headers are the column values... example below...

Jim	Diane	Frank	Sharon
Expert	Admin	Reviewer	Researcher

so, when the Expand {0} step fires, the first row becomes headers, which makes them appear correct, but there is no data in the columns... any suggestions?

Thanks

 

[Bill Szysz]

Hi Joe,
Please, attach a file with sample data (order of the columns and their amount should be the same as in the real file)

 

[joe1250]

View attachment Sample.xlsx

Ok attached is a sample of the output right before my previous attempts at pivoting and grouping... the last step of code was #"Reordered Columns1"

It appears there are some duplicate rows that could be removed before any further steps... just noticed after I attached the file though...

Thanks!

 

[Bill Szysz]

Check this file.... is that what you want?

 

[joe1250]

BEAUTIFUL!!! Thanks so much!