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

smpa01 Senior Member
Senior Member

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.

smpa01 Senior Member
Senior Member

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.

smpa01 Senior Member
Senior Member

Re: Lookup based on multiple criteria

This is the error I am getting

 

Capture99.PNG

Highlighted
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

smpa01 Senior Member
Senior Member

Re: Lookup based on multiple criteria

THanks for your time. It is resolved now.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)