cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarcelBeug Super Contributor
Super Contributor

Re: Lookup based on multiple criteria

You didn't test with my last code.

 

These are the results I get:

 

Lookup based on multiple criteria - 3.png

 

From the code in this post:

 

Lookup based on multiple criteria - 4.png

 

Specializing in Power Query Formula Language (M)

View solution in original post

Super User I
Super User I

Re: Lookup based on multiple criteria

Thanks Marcel a lot. Yes it was my Typo which did not return the desired result otherwise the code works with all the conditions.

 

I want to add another level to it and I will post in few days.

Super User I
Super User I

Re: Lookup based on multiple criteria

Hi Marcel,

 

Hope you are well. I have added another level (Vendor) to the previous spreadsheet.

 

The logic is if the Distinct Manager per State-Vendor = 1 then #Min BU per Manager-Vendor else Allocation Business Unit. I followed your code and it is working fine with my requirement. It can calculate the Header BU as I desired. However, when the data loads it is generating error and I have no clue why it happens.

 

If I don't load the data and create only connection and use a pivot to get the connection, I don't se any error. It only occurs if

 

If you can please take a look and advise how to get rid of it, would be great.

 

https://drive.google.com/file/d/0B7imabOIHE8QTTFBLWw4aFU5dzg/view?usp=sharing

 

Thank you in advance.

Super User I
Super User I

Re: Lookup based on multiple criteria

This is the error I am getting

 

Capture99.PNG

MarcelBeug Super Contributor
Super Contributor

Re: Lookup based on multiple criteria

Can't spend more time on this. The problem is with column Distinct Mgrs per S-V in query MLT_V_FM_ST (2)

 

It is defined as text, while the content are numbers. This is no error for Power Query, but it is an error when data is loaded to Excel.

(as it is no error for Power Query, you don't get any error records in your error query).

 

So change the type to (whole) number and the issue is solved.

Specializing in Power Query Formula Language (M)

View solution in original post

Super User I
Super User I

Re: Lookup based on multiple criteria

THanks for your time. It is resolved now.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors