Group by text and result only differences.

[Renato_mon]

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

[AlienSx · Accepted 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

[jgordon11]

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

[nickvanmaele]

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]

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!