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

Value compare using multiple resolution column logic

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      
NameResolution ID1Resolution ID2Value AValue BValue CValue D
company 1abca1231.32.43.52.4
company 2 a4561.52.43.21.2
company 3defa1991.42.31.41.4
company 4gqq 1.32.21.81.7
company 5tgga3111.21.62.52.4
company 6frea5551.71.82.82.6
company 7jgqa9991.71.12.61.3
company 8 a2221.82.32.13.2
company 9bgqa7611.22.42.33.2

 

Source2     
Resolution ID1Resolution ID2Value AValue BValue CValue D
abca1231.32.43.52.4
cdda4561.52.43.21.2
 a1991.41.41.41.4
gqqa3221.32.21.81.7
 a3111.91.61.61.1
frea5551.71.82.82.6
jgq 1.71.12.61.3
 a2221.82.32.11.9
ntea1011.22.42.33.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

 

1 ACCEPTED SOLUTION
ImkeF
Super User II
Super User II

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

View solution in original post

2 REPLIES 2
ImkeF
Super User II
Super User II

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

View solution in original post

@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.  

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors