PQ - Grouping columns in rows and get data marked by x together

[sandy666]

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

// 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

[sandy666]

with M above we achieved what was intended but the problem was that we cannot add additional columns (data, marker) to the source data and achieve the same result without editing the M itself.

so here is source data extended by additional columns

A

A.X

B

B.X

C

C.X

D

D.X

E

E.X

F

F.X

G

G.X

Bob

Sara

Ford

x

item01

aa

h01

x

s0

Joe

x

Mary

x

Fiat

item02

x

bb

x

s1

x

Dean

Cathy

Porsche

x

item03

cc

h02

s2

Mark

GMC

x

item04

x

h03

x

s3

Sergio

x

item05

s4

x

and achieved result without interfering with the source M

Group	Result
A	Joe, Sergio
B	Mary
C	Ford, Porsche, GMC
D	item02, item04
E	bb
F	h01, h03
G	s1, s4

after adding columns, you just need to refresh the result table

// Query1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TBD = Table.TransformColumns(Table.Transpose(Table.DemoteHeaders(Source)), {{"Column1", each Text.BeforeDelimiter(_, "."), type text}}),
    Grp = Table.Group(TBD, {"Column1"}, {{"A", each Text.Combine([Column2]), type nullable text}, {"B", each Text.Combine([Column3]), type nullable text}, {"C", each Text.Combine([Column4]), type nullable text}, {"D", each Text.Combine([Column5]), type nullable text}, {"E", each Text.Combine([Column6]), type nullable text}}),
    UNP = Table.UnpivotOtherColumns(Table.PromoteHeaders(Table.Transpose(Grp), [PromoteAllScalars=true]), {}, "Group", "Value"),
    Asc = Table.Sort(Table.Group(Table.SelectRows(UNP, each Text.EndsWith([Value], "x")), {"Group"}, {{"All", each _, type table [Group=text, Value=text]}}),{{"Group", Order.Ascending}}),
    TSC = Table.SelectColumns(Table.TransformColumns(Table.AddColumn(Asc, "Data", each [All][Value]), {"Data", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),{"Group", "Data"})
in
    TSC