Basically, I have this dummy table:
| People | Group ID |
|---|---|
| Albert | 1 |
| Bernard | 1 |
| Charles | 2 |
| Daniel | 2 |
| Elizabeth | 3 |
| Francis | 3 |
And what I would like to have is this:
| People 1 | People 2 | Group ID |
|---|---|---|
| Albert | Bernard | 1 |
| Charles | Daniel | 2 |
| Elizabeth | Francis | 3 |
I tried to pivot and unpivot here and there mindlessly to no avail, any ideas?
In powerquery,
Right-click the GroupID column and Group By...
Allow the default options and hit ok
Change the last part of the formula in the formula bar (or in home...advanced editor...) from
= Table.Group(Source, {"Group ID"}, {{"Count", each Table.RowCount(_), type number}})
to
= Table.Group(Source, {"Group ID"}, {{"Count", each Text.Combine(List.Transform([People], Text.From), ","), type text}})
that combines the People column into one column separated by commas
Then right click that column and split column by delimiter, for each occurrence of a comma
Full sample code:
let Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Group ID"}, {{"Count", each Text.Combine(List.Transform([People], Text.From), ","), type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Count", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Count.1", "Count.2"})
in #"Split Column by Delimiter"
//fancy version that includes Column titles and auto adjusts for dynamic number of columns
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Group ID"}, {{"Count", each Text.Combine(List.Transform([People], Text.From), ","), type text}}),
DynamicColumnList = List.Transform({1..List.Max(Table.AddColumn(#"Grouped Rows", "Custom", each List.Count(Text.PositionOfAny([Count], {","}, Occurrence.All)))[Custom])+1}, each "Person." & Text.From(_)),
#"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows","Count",Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),DynamicColumnList )
in #"Split Column by Delimiter"
Another way ....
Right-click the GroupID column and Group By...
Allow the default options and hit ok
Change the last part of the formula in the formula bar (or in home...advanced editor...) from
= Table.Group(Source, {"Group ID"}, {{"Count", each Table.RowCount(_), type number}})
to
= Table.Group(Source, {"Group ID"}, {{"count", each Table.AddIndexColumn(_, "Index", 1, 1), type table}})
Use arrows atop new column and expand [x] People and [x] Index
Click select index column, transform pivot, choose People as value, advanced options, don't aggregate
full sample code
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Group(Source, {"Group ID"}, {{"count", each Table.AddIndexColumn(_, "Index", 1, 1), type table}}),
#"Expanded count" = Table.ExpandTableColumn(Group, "count", {"People", "Index"}, {"People", "Index"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded count", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded count", {{"Index", type text}}, "en-US")[Index]), "Index", "People")
in #"Pivoted Column"
