Comparing Multiple Columns in Rows between Two Queries

[k_sun]

Hi

I've 2 queries with around 6000 rows and 40 identical columns each. Out of these 40 columns i need to do a validation for around 30 columns to identify how many records do not have the same data.

I've tried merge queries option to import matching data from one query to the other and seperate custom column to validate the existing data and imported values. But this I've to seperately do for all 30 columns seperately since there could be different set of mismatching records in all 30 instances. 

I believe there should be an easy and efficient way to manage this scenario which will not impact on the dashboard performances as well.

Please help

Query 1
Prod ID	Colour	Size	Price	Qty
1	Black	20	100	1000
2	Blue	18	100	1500
3	Red	20	100	1000
4	Black	19	90	1250
5	White	18	95	1000

Query 2
Prod ID	Colour	Size	Price	Qty
1	Black	30	100	990
2	Purple	18	80	1500
3	Red	20	100	900
4	Blue	20	90	1250
5	White	18	100	800

Expected Output in Dashboard

Colour
ID	Query 1	Query 2
4	Black	Blue

Size
ID	Query 1	Query 2
1	20	30
4	19	20

Price
ID	Query 1	Query 2
2	100	80
5	95	100

Qty
ID	Query 1	Query 2
1	1000	990
3	1000	900
5	1000	800

[Mariusz · Accepted solution]

Hi @k_sun 

You can try the below if it performs any better 

Query 1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKT1HwdFHSUXLOz8kvLQIygjOrUoFUQFFmMogOLKlUitWJVjIEsp1yEpOzgbSRAZAwNICSBmB5I7B8KUiLoQVC2hQqbQzkBKWm4NBsgmS4oSWQsARLG5lCpE2BnPCMzBK44ZamcN2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Prod ID", Int64.Type}, {"Colour", type text}, {"Size", Int64.Type}, {"Price", Int64.Type}, {"Qty", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Prod ID"}, "Attribute", "Query1")
in
    #"Unpivoted Columns"

Query 2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKT1HwdFHSUXLOz8kvLQIygjOrUoFUQFFmMogOLKlUitWJVjIEsp1yEpOzgbSxAZAwNACRlpYGYGkjkJbSooIckB5DCyBhAVZkagCRNwZyglJTgKQRkmaopAnY7NJUmKwlWImRKUTWFMgJz8gsgZsM0WwB0hwLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Prod ID", Int64.Type}, {"Colour", type text}, {"Size", Int64.Type}, {"Price", Int64.Type}, {"Qty", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Prod ID"}, "Attribute", "Query2")
in
    #"Unpivoted Columns"

Mredge

let
    Source = Table.NestedJoin(Query1, {"Prod ID", "Attribute"}, Query2, {"Prod ID", "Attribute"}, "Query2", JoinKind.LeftOuter),
    #"Expanded Query2" = Table.ExpandTableColumn(Source, "Query2", {"Query2"}, {"Query2"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Query2", each [Query1] <> [Query2])
in
    #"Filtered Rows"

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[k_sun]

Thanks a lot @Mariusz. It worked!!