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

J

joe1250

New member
Joined
Feb 2, 2018
Messages
14
Reaction score
0
Points
0
Excel Version(s)
2016
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 ResponsibilityAssigned To
0001ExpertJim
0001AdminDiane
0001ReviewerFrank
0001ResearchSharon

Current Result After Pivot and Group By:
File #ExpertAdminReviewerResearch
0001Jimnull
nullnull
0001nullDianenullnull
0001nullnullFranknull
0001nullnullnullSharon

Desired Outcome:
File #ExpertAdminReviewerResearch
0001JimDianeFrankSharon

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!
 
Try this code below ( "Table 1" is your original table and not after pivoting and grouping)
Code: 
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}"
 
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...


JimDianeFrankSharon
ExpertAdminReviewerResearcher

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 :)
 
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!
 
BEAUTIFUL!!! Thanks so much!
 
You must log in or register to reply here.
Back
Top