Master Data potential duplicate analysis

[Martin0011]

I have a table "Vendor_TAX" where there is below list : 

Supplier ID	TAX1	TAX2	TAX3
1	A
2		A
3			A
4	B
5	B
6		B
7			C
8			D
9		E
10		C

I need to identify the potential duplicate crossing all taxes - for each line, it should chetk : 

- If the TAX1 = TAX1 or TAX 2 or TAX3 for another supplier

- If the TAX2 = TAX1 or TAX 2 or TAX3 for another supplier

- If the TAX3 = TAX1 or TAX 2 or TAX3 for another supplier

Output would like like : 

Supplier ID	TAX1	TAX2	TAX3	Duplicate Group	Duplicate List
1	A			1	1,2,3
2		A		1	1,2,3
3			A	1	1,2,3
4	B			2	4,5,6
5	B			2	4,5,6
6		B		2	4,5,6
7			C	3	7,1
8			D
9		E
10		C		3	7,1

It would group all the supplier that match the same taxes as well as listing them. 

I can't do this into Power Querry or in the table because I need to formula to follow the fitler input by the end user. 

Any idea how to acheive this ? 

[Anonymous]

Hi @Martin0011 ,

Please try like:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYhCK1YlWMoKwHWF8Y6gcSAjENwGynJDUm6LxzSBsJxjfHK7fGcy3gPNdwHxLCMcVpt7QAKYaJBALAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Supplier ID" = _t, TAX1 = _t, TAX2 = _t, TAX3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Supplier ID", Int64.Type}, {"TAX1", type text}, {"TAX2", type text}, {"TAX3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Supplier ID"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> "")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Value"}, {{"Data", each _, type table [Supplier ID=nullable number, Attribute=text, Value=text]}}),
    Custom1 = Table.SelectRows(#"Grouped Rows", each List.Count(List.Distinct([Data][Attribute]))>1),
    #"Added Index" = Table.AddIndexColumn(Custom1, "Duplicate Group", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Duplicate List", each Text.Combine(List.Transform([Data][Supplier ID],each Text.From(_)),",")),
    #"Expanded Data" = Table.ExpandTableColumn(#"Added Custom", "Data", {"Supplier ID"}, {"Supplier ID"}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Supplier ID"}, #"Expanded Data", {"Supplier ID"}, "Expanded Data", JoinKind.LeftOuter),
    #"Expanded Expanded Data" = Table.ExpandTableColumn(#"Merged Queries", "Expanded Data", {"Duplicate Group", "Duplicate List"}, {"Duplicate Group", "Duplicate List"})
in
    #"Expanded Expanded Data"

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Martin0011]

It works super great and give me idea. But it would 100% fit the requirement as this is stored in the query. 

It must be dynamic based on the filter the end user will define. 

Let's assume I have a Active/Not Active dimension : 

Supplier IDStatus

1	Active
2	Not Active
3	Active
4	Not Active
5	Not Active
6	Not Active
7	Active
8	Active
9	Active
10	Active

Based on the above, the result should remove all the Not Active prior to attirbute a group and define the duplicate list. 

Reason why I was looking to a measure.