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

AA.XBB.XCC.XDD.X

BobSaraFordxitem01

JoexMaryxFiatitem02x

DeanCathyPorschexitem03

MarkGMCxitem04x

Sergioxitem05


expected result:


GroupResult

AJoe, Sergio

BMary

CFord, Porsche, GMC

Ditem02, 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.De moteHeaders(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


GroupResult


AJoe, Sergio

BMary

CFord, Porsche, GMC

Ditem02, 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


AA.XBB.XCC.XDD.XEE.XFF.XGG.X

BobSaraFordxitem01aah01xs0

JoexMaryxFiatitem02xbbxs1x

DeanCathyPorschexitem03cch02s2

MarkGMCxitem04xh03xs3

Sergioxitem05s4x


and achieved result without interfering with the source M


GroupResult

AJoe, Sergio

BMary

CFord, Porsche, GMC

Ditem02, item04

Ebb

Fh01, h03

Gs1, 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.Demot eHeaders(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(Tab le.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.A ddColumn(Asc, "Data", each [All][Value]), {"Data", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),{"Group", "Data"})
in
TSC