Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Product | market | Part |
x1 | AX2 | ZZZ |
x2 | AX2 | GGG |
x2 | AX2 | YYY |
My DB exemple:
Product | market | Part |
x1 | AX1 | AAA |
x1 | AX1 | BBB |
x1 | AX1 | CCC |
x1 | AX1 | DDD |
x1 | AX1 | EEE |
x1 | AX1 | FFF |
x1 | AX2 | AAA |
x1 | AX2 | BBB |
x1 | AX2 | CCC |
x1 | AX2 | DDD |
x1 | AX2 | EEE |
x1 | AX2 | FFF |
x1 | AX2 | ZZZ |
x2 | AX1 | AAA |
x2 | AX1 | BBB |
x2 | AX1 | CCC |
x2 | AX1 | DDD |
x2 | AX1 | EEE |
x2 | AX1 | FFF |
x2 | AX2 | AAA |
x2 | AX2 | BBB |
x2 | AX2 | CCC |
x2 | AX2 | DDD |
x2 | AX2 | EEE |
x2 | AX2 | FFF |
x2 | AX2 | GGG |
x2 | AX2 | YYY |
Solved! Go to Solution.
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
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
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
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?
"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!