Search code examples
excelpowerquery

How to Reconfigure Excel Columns so one is a group and the other are the data in columns in using PowerQuery

Essentially I have two columns, one is a group column, the other is a list of clients. Each client is in a group. I don't want duplicate groups, each group should have all the clients in one line next to it.

So essentially from the top example to the bottom example - using PowerQuery

I've tried using Pivot rows, Unpivot rows and group by functions in PowerQuery, however, it hasn't worked as anticipated and due to my novice ability I'm out of ideas!

Solution

  • I will assume this is what you wanted, starting with data on left, ending with data on right, with code below pasted into home...advanced editor... preserving your first row in place of mine

    let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"data", each Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type), type table}}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Column2", "Index"}, {"Column2", "Index"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded data", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded data", {{"Index", type text}}, "en-US")[Index]), "Index", "Column2")
in #"Pivoted Column"

    enter image description here

    Click select Column1, right click, group by and use operation All Rows

    Go in the formula bar and replace the word each and everything after it with

    each Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type), type table}})

    Use arrows atop the new column to expand the index and the second column. Click select the index column, transform ... pivot column... and use the second column as the data