Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Closest lookup date in Power Query / M-language

Hi, In the Excel table "Purchases" I want to add a New column where I use the Date row to lookup an exchange rate in the "Exchange Rates" table and calculate the new currency cost column based on th...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Anonymous 

     

    Try this new custom column

     

    =let mydate=[Date] in 
Table.Max(
Table.SelectRows(#"Exchange Rates",each [Date] <= mydate),"Date")[Rate]
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
7 years ago

Anonymous 

 

Try this new custom column

 

=let mydate=[Date] in 
Table.Max(
Table.SelectRows(#"Exchange Rates",each [Date] <= mydate),"Date")[Rate]