Search code examples
mysqlpowerbipowerquerym

performing a subquery in an each in M language in Power BI

I am converting SQL in various flavors into Power BI. I have the following query/code which I am trying to represent in the M syntax:

while($row = $result->fetch_assoc())
{
    $rate = SELECT CAD FROM ExchangeRate WHERE date<='" . $row['d'] . "' ORDER BY date DESC LIMIT 1
    $monthlySalesUSD += $daily/$rate;
}

Basically, it looks up the exchange rate for the date of a particular set of transactions, then divides the sales figures for that day by the exchange rate for that day.

In Power BI Advanced Query, I have the following excerpt defined:

    #"Added Custom1" = Table.AddColumn(#"Changed Type", "SalesUSD", each [Inserted Rounding] / List.First(Table.Column(Table.First(Table.SelectRows(Table.Sort(warehouse_ExchangeRate,{{"date", Order.Descending}}),each [date] <= #"Changed Type"[DATELASTFULFILLMENT])),"CAD"))),

#"Changed Type" Contains the daily transaction totals and the dates. The totals are in [Inserted Rounding] and the dates are in [DATELASTFULFILLMENT]. So basically, for each row in #"Changed Type", I want to get the date, look up the exchange rate, then divide the sales by that rate. The piece I cannot seem to get is how to pull in the date from the current row in the each.

Solution

  • I found the answer thanks to MarcelBeug pointing me in the right direction. I found this post which led me to the fact that you can do lambdas in an add column. This gave me the following steps:

    #"Fixed Join" = Table.AddColumn(#"Inserted Date", "ExchangeRate",
        (Earlier) => Table.SelectRows(warehouse_ExchangeRate,
                     each [date] <= Earlier[Date])),
#"Expanded ExchangeRate" = Table.ExpandTableColumn(#"Fixed Join", "ExchangeRate", {"CAD"}, {"CAD"})

    This gives me the closest match that is less than the date of the sale, which I can then just divide into the sale to give me the USD value, then select the columns I need from that.