Code:
// 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
Bookmarks