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 am trying to compare the rows in 2 tables to return the number fo difference in a single row with the same key:
Table 1
Entity Param 1 Param 2 Param 3 Param 4 Param 5 Param 6 Param 7 Param 8 Param 9 Param 10 Param 11
Test | 11 | 2 | 3 | 4 | 5 | 6 | 7 | 19 | 9 | 12 | 11 |
Table 2
Entity Param 1 Param 2 Param 3 Param 4 Param 5 Param 6 Param 7 Param 8 Param 9 Param 10 Param 11
Test | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 |
I would expect to return
Test = 4 differences (utlimately I would like to drill to those diefferneces)
Can anyone help
Solved! Go to Solution.
The unpivotting is done in the Query Editor and will be applied on every refresh of the data. This means Power BI will do;
1. retrieve the data in original format
2. Applies all steps as per Query Definition
3. Updates all visuals.
In step two is the unpivoting of the tables and will persist 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Kudo's are welcome 🙂
Proud to be a Super User!
Interesting question, I gave it a shot by doing the following;
I've created two tables with Entity and 5 Param columns:
Comparing these in current form is very difficult, so I unpivoted the Param-columns in both;
Now, we can create a calculated column in one of the tables (I've chosen Table1) and entity by entity (in this case just 'Test') check whether the value corresponding with the Param(X) is the same in Table 2. The formula is:
IsNotTheSame = CALCULATE(COUNTA(Table1[ParamValue]), FILTER(Table2, Table2[Entity] = Table1[Entity] && Table2[Params] = Table1[Params] && Table2[ParamValue] <> Table1[ParamValue]))
Now, you can use this column in calculating how many discrepancies there are between Entities in Table1 and Table2:
Please mark this answer as Solution if this answered your question 🙂
Proud to be a Super User!
Hi djerro123
Nice neat answer but......can I automate the unpivot and rename functions as my source data is in the 'pivotted' format?
Regards
Simon
The unpivotting is done in the Query Editor and will be applied on every refresh of the data. This means Power BI will do;
1. retrieve the data in original format
2. Applies all steps as per Query Definition
3. Updates all visuals.
In step two is the unpivoting of the tables and will persist 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Kudo's are welcome 🙂
Proud to be a Super User!
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |