Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PSConnor
New Member

Mimic Lookup(2,1/ - Don't know where to start - M Power Query

Hi,

 

I'm looking for some advice, or someone to point me in the right direction. I'm trying to mimic a formula and i just don't even know where to start or how to even structure this problem in M.

 

 

 

=LOOKUP(2,1/((Sheet1!$Q$2:$Q$72266=MAXIFS(Sheet1!$Q$2:$Q$72266,Sheet1!$D$2:$D$72266,'Price Variations'!A5768,Sheet1!$F$2:$F$72266,'Price Variations'!B5768,Sheet1!$H$2:$H$72266,'Price Variations'!C5768))*(Sheet1!$D$2:$D$72266='Price Variations'!A5768)*(Sheet1!$F$2:$F$72266='Price Variations'!B5768)*(Sheet1!$H$2:$H$72266='Price Variations'!C5768)),Sheet1!$K$2:$K$72266)

 

 

 

 

 

Q:Q = Order Date

D,F,H = Vendor, Part No, Qty

K:K = Unit Price

 

As you can see, i'm trying to bring back the earliest unit price where DFH match. 

 

Now i've got the exact same setup in power query, two tables. Both with the exact same headers and layout, and i'm trying to do the exact same. Bring back the earliest unit price on record for the three columns that match.

 

Can somone give me an example of how i can create a custom column that does the same thing?

 

Thanks

 

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

Fastest way would be to create a table of distinct key-combinations, sort (and buffer) it according to the Order Date and merge that (on the 3 key columns) to your original table. Expand field "unit price" and you're done.

 

Simply reference lookup table, do the transformations, but unselect "load to data model" is you need to use that table just for the lookup-purpose.

 

why buffer your sort?: https://community.powerbi.com/t5/Community-Blog/Bug-warning-for-Table-Sort-and-removing-duplicates-i...

 

 

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

4 REPLIES 4
v-juanli-msft
Community Support
Community Support

Hi @PSConnor 

For example, i have Sheet1 data as below

Order Date Vendor Part No Qty Unit Price
1/1/2019 a 1 10 1
1/2/2019 a 2 11 2
1/3/2019 a 3 12 3
1/4/2019 a 4 13 4
1/5/2019 a 5 14 5
1/6/2019 b 6 15 6
1/7/2019 b 7 16 7
1/8/2019 b 8 17 8
1/9/2019 b 9 18 9
1/10/2019 b 10 19 10


'Price Variations'

A B C
a 1 10
a 2 11
b 3 12
b 4 13

What is the end result you want?

 

Best Regards
Maggie

Hi @v-juanli-msft 

 

So imagine you had this following example:

 

Order DateVendorPart NoQtyUnit Price
01/01/2019a1101
01/02/2019a1102
01/03/2019a1103
01/04/2019a1104
01/05/2019a1105
01/07/2019b2206
01/06/2019b2207
01/08/2019b2208
01/09/2019b2209
01/10/2019b22010

 

This would be the output:

VendorPart NoQtyUnit Price
a1101
b2207

 

It would return the unit price of the entry that had the earliest Order Date, where Vendor,Part,Qty matched. I'm just trying to replicate the same with two tables i have in Power Query.

 

Hope that clears things up.

 

Thanks

Hi

please check the solution enclosed.

(I've adjusted the source data a bit, to return a match for the 2nd vendor).

Performance could be improved ( https://www.thebiccountant.com/2017/05/29/performance-tip-partition-tables-crossjoins-possible-power... )  , but this way allows to follow up some of the logic. 

 

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
Super User
Super User

Fastest way would be to create a table of distinct key-combinations, sort (and buffer) it according to the Order Date and merge that (on the 3 key columns) to your original table. Expand field "unit price" and you're done.

 

Simply reference lookup table, do the transformations, but unselect "load to data model" is you need to use that table just for the lookup-purpose.

 

why buffer your sort?: https://community.powerbi.com/t5/Community-Blog/Bug-warning-for-Table-Sort-and-removing-duplicates-i...

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors