PQ - Grouping columns in rows and get data marked by x together
We have a source table where the first column is data and the second is a selection column with an x marker (and so it is with each subsequent column: data column and marker column). What we want to achieve is to group the columns without the marker columns and get the data for the corresponding columns separated by commas.
see example:
source table
| A |
A.X |
B |
B.X |
C |
C.X |
D |
D.X |
| Bob |
|
Sara |
|
Ford |
x |
item01 |
|
| Joe |
x |
Mary |
x |
Fiat |
|
item02 |
x |
| Dean |
|
Cathy |
|
Porsche |
x |
item03 |
|
| Mark |
|
|
|
GMC |
x |
item04 |
x |
| Sergio |
x |
|
|
|
|
item05 |
|
expected result:
| Group |
Result |
| A |
Joe, Sergio |
| B |
Mary |
| C |
Ford, Porsche, GMC |
| D |
item02, item04 |
Code:
// Query1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Text = Table.TransformColumnTypes(Source,{{"A", type text}, {"A.X", type text}, {"B", type text}, {"B.X", type text}, {"C", type text}, {"C.X", type text}, {"D", type text}, {"D.X", type text}}),
MC1 = Table.CombineColumns(Text,{"A", "A.X"},Combiner.CombineTextByDelimiter("#", QuoteStyle.None),"A"),
MC2 = Table.CombineColumns(MC1,{"B", "B.X"},Combiner.CombineTextByDelimiter("#", QuoteStyle.None),"B"),
MC3 = Table.CombineColumns(MC2,{"C", "C.X"},Combiner.CombineTextByDelimiter("#", QuoteStyle.None),"C"),
MC4 = Table.CombineColumns(MC3,{"D", "D.X"},Combiner.CombineTextByDelimiter("#", QuoteStyle.None),"D"),
UOC = Table.UnpivotOtherColumns(Table.Transpose(Table.DemoteHeaders(MC4)), {"Column1"}, "Attribute", "Value"),
Trim = Table.AddColumn(Table.SelectRows(UOC, each Text.Contains([Value], "#x")), "Result", each Text.Trim(Text.TrimEnd([Value],"x"),"#")),
TB = Table.TransformColumns(Table.SelectColumns(Trim,{"Column1", "Result"}), {{"Column1", each Text.BeforeDelimiter(_, "."), type text}}),
List = Table.AddColumn(Table.Group(TB, {"Column1"}, {{"Count", each _, type table [Column1=text, Result=text]}}), "Result", each [Count][Result]),
Result = Table.RenameColumns(Table.TransformColumns(List, {"Result", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),{{"Column1", "Group"}})
in
Result
| Group |
Result |
| A |
Joe, Sergio |
| B |
Mary |
| C |
Ford, Porsche, GMC |
| D |
item02, item04 |