Results 1 to 2 of 2

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

  1. #1
    Sir Moderator sandy666's Avatar
    Join Date
    May 2018
    Posts
    236
    Rep Power
    7

    Cool 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
    Last edited by sandy666; 07-31-2023 at 01:24 AM. Reason: updating
    sandy
    I know you know but I forgot my Crystal Ball and don't know what you know also I may not remember what I did weeks, months or years ago so answer asap. Thinking doesn't hurt

  2. #2
    Sir Moderator sandy666's Avatar
    Join Date
    May 2018
    Posts
    236
    Rep Power
    7
    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

    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
    Last edited by sandy666; 07-26-2023 at 01:46 PM.
    sandy
    I know you know but I forgot my Crystal Ball and don't know what you know also I may not remember what I did weeks, months or years ago so answer asap. Thinking doesn't hurt

Similar Threads

  1. Replies: 5
    Last Post: 07-30-2014, 07:51 PM
  2. Replies: 8
    Last Post: 04-20-2014, 10:15 PM
  3. seprate data in rows to columns
    By CORAL in forum Excel Help
    Replies: 2
    Last Post: 03-10-2014, 08:18 AM
  4. Replies: 3
    Last Post: 08-18-2013, 08:59 PM
  5. Move data from rows into columns for every unique value
    By mahmoud-lee in forum Excel Help
    Replies: 4
    Last Post: 06-13-2013, 03:02 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •