Forum Discussion

Renato_mon's avatar
Renato_mon
Frequent Visitor
3 years ago
Solved

Group by text and result only differences.

Hello,

 

I'd like to group by text and result only the differences between each grouped row.

I appreciate the help!

Thanks

 

The result I expect:

ProductmarketPart
x1AX2ZZZ
x2AX2GGG
x2AX2YYY

 

My DB exemple:

ProductmarketPart
x1AX1AAA
x1AX1BBB
x1AX1CCC
x1AX1DDD
x1AX1EEE
x1AX1FFF
x1AX2AAA
x1AX2BBB
x1AX2CCC
x1AX2DDD
x1AX2EEE
x1AX2FFF
x1AX2ZZZ
x2AX1AAA
x2AX1BBB
x2AX1CCC
x2AX1DDD
x2AX1EEE
x2AX1FFF
x2AX2AAA
x2AX2BBB
x2AX2CCC
x2AX2DDD
x2AX2EEE
x2AX2FFF
x2AX2GGG
x2AX2YYY
Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    Hi, Renato_mon 

    let
    Source = your_table,
    g = Table.Group(Source, "Part", {{"Count", each Table.RowCount(_)}, {"a", each _}}),
    flt = Table.SelectRows(g, each ([Count] = 1)),
    z = Table.Combine(flt[a])
in
    z

4 Replies

  • jgordon11's avatar
    jgordon11
    Resolver II
    3 years ago
    let
    Source = --- code for your source table ---,
    tbl = Table.NestedJoin(Source, {"Product", "Part"}, Source, {"Product", "Part"}, "Join", JoinKind.LeftOuter),
    tbl1 = Table.RemoveColumns(Table.SelectRows(tbl, each Table.RowCount([Join]) =1), {"Join"})
in
    tbl1
  • AlienSx's avatar
    AlienSx
    Super User
    3 years ago

    Hi, Renato_mon 

    let
    Source = your_table,
    g = Table.Group(Source, "Part", {{"Count", each Table.RowCount(_)}, {"a", each _}}),
    flt = Table.SelectRows(g, each ([Count] = 1)),
    z = Table.Combine(flt[a])
in
    z
  • nickvanmaele's avatar
    nickvanmaele
    Advocate II
    3 years ago

    Hi Renato_mon 

     

    When you say "difference", please define exactly what that means.

     

    If I would add two more lines to the table, (x1, AX2, MMM) and (x2, AX2, MMM), what would be the expected result?

     

    What if I did not add the above two lines, but (x1, AX1, MMM) and (x2, AX2, MMM), what would be the expected result?

    • Renato_mon's avatar
      Renato_mon
      Frequent Visitor
      3 years ago

      Hi nickvanmaele 

       

      "Difference" means only value that are unique in all Product and Market.

      In the both question, the result will be the same. The Part MMM will use in two Product/Market.

      Write in the other way with 2 examples:

       

      My DB have about 100 Products for 10 Markets, each one with about 150 Parts (15000 rows).

      I hope was clear with my doubt.

      Thanks!