cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
k_sun Frequent Visitor
Frequent Visitor

Comparing Multiple Columns in Rows between Two Queries

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 IDColourSizePriceQty
1Black201001000
2Blue181001500
3Red201001000
4Black19901250
5White18951000

 

Query 2
Prod IDColourSizePriceQty
1Black30100990
2Purple18801500
3Red20100900
4Blue20901250
5White18100800

 

Expected Output in Dashboard

Colour
IDQuery 1Query 2
4BlackBlue
Size
IDQuery 1Query 2
12030
41920
Price
IDQuery 1Query 2
210080
595100
Qty
IDQuery 1Query 2
11000990
31000900
51000800
1 ACCEPTED SOLUTION

Accepted Solutions
Mariusz Senior Member
Senior Member

Re: Comparing Multiple Columns in Rows between Two Queries

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.

2 REPLIES 2
Mariusz Senior Member
Senior Member

Re: Comparing Multiple Columns in Rows between Two Queries

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 Frequent Visitor
Frequent Visitor

Re: Comparing Multiple Columns in Rows between Two Queries

Thanks a lot @Mariusz. It worked!!