Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Not sure if this is best suited for Power Query or even Power BI but I have created some dashboards already based on more simple resolution logic (1 instead of 2 or more). In this scenario, the goal is to compare values between the source vs the output of the data to ensure that the values are the same. There is more logic involved with normalizing data but I am going to simplify the scenario.
The data that comes in has columns that are reference data and columns that are values. The size of the query is about 50,000 rows and 850 columns. As part of my processing columns, I remove a lot of the value columns to reduce about 300ish rows, varies based on the source.
In the past for other queries, there was only 1 resolution logic needed, so it was pretty straight forward. Here is the process that I took.
1. Take the data and unpivot the values (keep the reference data as columns).
2. create a new key column to join the data together by concatenating "resolution id1" & "Value", so we have a unique key for joining and lining up the data.
3. In the dashboard I can line up and do a value check.
Now, I have 2 resolution columns that I have to check, if I can match the 2 queries based on resolution column 1, then I will use it, if not then go to resolution column 2 and if that fails, then no match at all. (I capture that separately and report those out as well).
Here is an example:
Source1 | ||||||
Name | Resolution ID1 | Resolution ID2 | Value A | Value B | Value C | Value D |
company 1 | abc | a123 | 1.3 | 2.4 | 3.5 | 2.4 |
company 2 | a456 | 1.5 | 2.4 | 3.2 | 1.2 | |
company 3 | def | a199 | 1.4 | 2.3 | 1.4 | 1.4 |
company 4 | gqq | 1.3 | 2.2 | 1.8 | 1.7 | |
company 5 | tgg | a311 | 1.2 | 1.6 | 2.5 | 2.4 |
company 6 | fre | a555 | 1.7 | 1.8 | 2.8 | 2.6 |
company 7 | jgq | a999 | 1.7 | 1.1 | 2.6 | 1.3 |
company 8 | a222 | 1.8 | 2.3 | 2.1 | 3.2 | |
company 9 | bgq | a761 | 1.2 | 2.4 | 2.3 | 3.2 |
Source2 | |||||
Resolution ID1 | Resolution ID2 | Value A | Value B | Value C | Value D |
abc | a123 | 1.3 | 2.4 | 3.5 | 2.4 |
cdd | a456 | 1.5 | 2.4 | 3.2 | 1.2 |
a199 | 1.4 | 1.4 | 1.4 | 1.4 | |
gqq | a322 | 1.3 | 2.2 | 1.8 | 1.7 |
a311 | 1.9 | 1.6 | 1.6 | 1.1 | |
fre | a555 | 1.7 | 1.8 | 2.8 | 2.6 |
jgq | 1.7 | 1.1 | 2.6 | 1.3 | |
a222 | 1.8 | 2.3 | 2.1 | 1.9 | |
nte | a101 | 1.2 | 2.4 | 2.3 | 3.2 |
Need to do the resolution:
In the tables above, if I look at Resolution ID1 and can resolve between Source1 and Source2, then I will use that to lineup the rows for the check. Examples:
1. Source1.Resolution ID1 = abc and Source2.Resolution ID2 = abc so that means I can do a compare.
2. company2, Source1.Resolution ID1 is null so it doesn't match anything in source2 (if I change the nulls to "x" or something), so it looks at Resolution ID2. (look at a456 can be resolved on both tables).
3. company3, Resolution ID1 doesn't match anything in Source2, so it looks at Resolution ID2 and it can then find a match to a199.
4. company9 doesn't match anything so I will not do a compare on values, so we can drop that one off completely
My original idea was to concatenate Resolution ID1, Resolution ID2 and Value (A, B, C, D, E, F, G.... ZZZ) so I can line up the data together in a join, but you can see based on examples, that would not work. You can see for company2, my key would be a456-Value A, a456-Value B, etc... and in source 2, it would be cdd-a456-Value A, cdd-a456-Value B.
If anyone was testing, then the end result here is I am identifying the scenarios where data do not match. If you plug in this data, here are the mismatches:
1. company3 - Value B
2. company5 - Value A, Value C and Value D
3. company8 - Value D
Note company9 is not included in the check as you cannot lineup any data at all, which is ok.
Any ideas how I can put in this resolution logic? Is my overall approach ok?
Cheers & Thank you!
Trieu
Solved! Go to Solution.
Hi @TbombToronto ,
I would unpviot twice:
1) Unpivot your values columns
2) Unpivot your Resoluitons-columns
3) Merge and fillter
see attached file
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @TbombToronto ,
I would unpviot twice:
1) Unpivot your values columns
2) Unpivot your Resoluitons-columns
3) Merge and fillter
see attached file
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@ImkeF , I didn't even think about doing the unpivot twice, but that definitely works! Thank you. Hopefully it doesn't create too much of a performance issue.