Duplicate Data and its value

[asrarkhan26]

I have two table of same type. I want to validate Col-1 of two table and its brand name of two table. If both Brand colume value match with both table then mention matched in 3rd column otherwise not matched

Table 1

 Col-1      Brand

 Printer     Canon

 Printer     Canon

Adapter    HP

Adapter    HP

Adapter    HP

Table 2

Col-1         Brand 

Printer       Canon

Printer       HP

Adapter     HP

Adapter     

Adapter     Nokia

Result 

Col-1       Brand     Result

Printer     Canon     Matched

Printer      Canon    Not Matched

Adapter    HP          Matched

Adapter    HP          Not Matched

Adapter    Nokia      Matched

Thanks

[tackytechtom · Accepted solution]

Hi @asrarkhan26 ,

How about this solution in Power Query:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

Table1 (result table):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKzCtJLVLSUXJOzMvPU4rVwS7mmJJYABHzCCBGIBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col-1" = _t, Brand = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Sorted Rows" = Table.Sort(#"Added Index",{{"Index", Order.Ascending}}),
    #"Merged Queries" = Table.NestedJoin(#"Sorted Rows", {"Col-1", "Brand", "Index"}, Table2, {"Col-1", "Brand ", "Index"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Index"}, {"Table2.Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table2", "Result", each if [Table2.Index] = null then "not matched" else "matched"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Table2.Index"})
in
    #"Removed Columns"

Table2:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKzCtJLVLSUXJOzMvPU4rVQRbzCAALOKYkFuAQQOP65WdnJirFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col-1" = _t, #"Brand " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col-1", type text}, {"Brand ", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[tackytechtom · Accepted solution]

Hi @asrarkhan26 ,

How about this solution in Power Query:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

Table1 (result table):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKzCtJLVLSUXJOzMvPU4rVwS7mmJJYABHzCCBGIBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col-1" = _t, Brand = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Sorted Rows" = Table.Sort(#"Added Index",{{"Index", Order.Ascending}}),
    #"Merged Queries" = Table.NestedJoin(#"Sorted Rows", {"Col-1", "Brand", "Index"}, Table2, {"Col-1", "Brand ", "Index"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Index"}, {"Table2.Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table2", "Result", each if [Table2.Index] = null then "not matched" else "matched"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Table2.Index"})
in
    #"Removed Columns"

Table2:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKzCtJLVLSUXJOzMvPU4rVQRbzCAALOKYkFuAQQOP65WdnJirFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col-1" = _t, #"Brand " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col-1", type text}, {"Brand ", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[asrarkhan26 · Accepted solution]

Yes, it working for me. I am delighted to see this solution. Thank You