Power Query - Price List using Date of Sale

[AllisterB]

I want to include in table Transactions the Price that was effective at the date of the Sales (as per table Pricelist).

I suspect that its the M Code e equivalent of Excel's Vlookup ... but I'm not sure how to go about it.

Any ideas I could try?


Thank you

[benishiryo]

in PQ:

1. Merge the 2 tables with Full Outer using the dates.

2. Add Conditional Column. If Date = null, then show Effective column. Else, show Date column.

3. Sort the new Conditional Column in ascending order.

4. Fill Down for the new Price column.

5. Remove the Effective Date column and Conditional column.

6. Filter away the null values.

you can watch this video as well:
https://www.youtube.com/watch?v=Fo-lGE6vgJM

[alansidman]

This link should help you to make it happen. I would first change the second table with pricing to a start and end date and then use this link to do your join.

https://radacad.com/dates-between-me...in-power-query

[AllisterB]

in PQ:

1. Merge the 2 tables with Full Outer using the dates.

2. Add Conditional Column. If Date = null, then show Effective column. Else, show Date column.

3. Sort the new Conditional Column in ascending order.

4. Fill Down for the new Price column.

5. Remove the Effective Date column and Conditional column.

6. Filter away the null values.

you can watch this video as well:
https://www.youtube.com/watch?v=Fo-lGE6vgJM

I now need to look up on two Columns - What is the Price for Customer ABC at date X.

Can I use the same approach or is there another approach in PQ - I've tried but I think the solution is only good for one column lookup.

Thanks

Allister