Hello all!
Is there a way to transpose a table after grouping? Or if you have a better suggestion, that would be great!
below is the data.
| District | School |
| Will | Smith |
| Will | Do |
| Some | Day |
| Some | Body |
| Some | One |
What I want to do is group the [District] and transpose the [School], is there a way to do this in power query? Results that I am looking for is below.
| District | School 1 | School 2 | School 3 |
| Will | Smith | Do | |
| Some | Day | Body | One |
Thanks in advance!
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"District"}, {{"Data", each _, type table [District=text, School=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"Index",1,1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"School", "Index"}, {"Custom.School", "Custom.Index"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.School")
in
#"Pivoted Column"
