Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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
So imagine you had this following example:
Order Date | Vendor | Part No | Qty | Unit Price |
01/01/2019 | a | 1 | 10 | 1 |
01/02/2019 | a | 1 | 10 | 2 |
01/03/2019 | a | 1 | 10 | 3 |
01/04/2019 | a | 1 | 10 | 4 |
01/05/2019 | a | 1 | 10 | 5 |
01/07/2019 | b | 2 | 20 | 6 |
01/06/2019 | b | 2 | 20 | 7 |
01/08/2019 | b | 2 | 20 | 8 |
01/09/2019 | b | 2 | 20 | 9 |
01/10/2019 | b | 2 | 20 | 10 |
This would be the output:
Vendor | Part No | Qty | Unit Price |
a | 1 | 10 | 1 |
b | 2 | 20 | 7 |
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
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