Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Select Rows From Another Tables With Unmatching Dates

Hello everyone, I had posted a message this morning and I thought I had found the solution but it seems that I still need help writing in PowerQuery. 

 

So, I remind my question, I had a table with my orders (named: OrdersSA18_PowerBI) and another with my exchange rates (named: Exchange Rate Table). 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I would have liked to add my exchange rate to my orders table. The problem being that I don't have an exchange rate for each day in my exchange rate table. So I would like to have my exchange rate for each order date so that : [Currency Code] = [Currency Code] and [Order Date] <= [Starting Date]

 

Here is what I Had written in "Add a new column":

 

But it doesn't work and doesn't give me what I want despite the fact that the syntax is correct.

 

Thank you very much in advance, you are a great help for the community.

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

     

    you should modify thie part of you script

    from

    [Currency Code] = [Currency Code] and [Order Date] <= [Starting Date]

     to

    OrdersSA18_PowerBI[Currency Code] = [Currency Code] and OrdersSA18_PowerBI[Order Date] <= [Starting Date]


    I couldn't test the expression obviously, but I hope it's enough to get you through the impasse

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Unfortunately I have the message that no column was found when I apply your solution as you can see on these screenshots

      Do you know how I can fix it? Thank you very much for your intervention.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        If you want to have a quick and useful answer, try to upload a file with the starting tables and the desired arrival table.

         

         

        I didn't realize it was inside anotherfunction  ( table.add.column)

        try this inside the box of addcolumn (colonne personaliseé)

         

         

         Table.SelectRows(tablename, (r)=>r[Currency Code] = [Currency Code] and r[Order Date] <= [Starting Date])