Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
107 | |
69 | |
68 | |
43 |
User | Count |
---|---|
148 | |
104 | |
102 | |
88 | |
66 |