M script for Custom Column

[rachaelnelson]

I have two datasets of sales data from two different divisions.  The tables do not contain the same columns but do have overlap.  I want to append the datasets together; however, one dataset contains calculated columns and when I append as new, the calculated columns do not come across.  I looked at creating a custom column using M language; however, his is my first exposure to M.

My formula is fairly straightforward but requires using another table to perform the mathematical calculation.  For simplicity sake, here is a breakdown of my tables needed for the custom column.

Exchange Rate Table contains:

Fiscal Start Date

Fiscal End Date

Exchange Rate

EU Sales Data Table contains:

Customer

Product

Quantity

EU Price

Sale Date

I need the following custom column:

Exchange Rate - lookup the exchange rate applicable to the sale date of each row

Once I have this, I can create two additional calculated columns using dax so that both datasets have the Total Sale amount for my visuals.

US Price  = Exchange Rate * EU Price

US Total Sale = US Price * Quantity

Can someone assist me with how I can code a custom column (using M) to lookup the exchange rate for each row in my EU Sales Data?  I already created the calculated columns using Dax but the columns do not append the values when I joined the datasets.

[TomMartens · Accepted solution]

Hey,

this is not that easy.

In the query editor add a custom column to your EU Sales Data table and enter the following formula

let outerDate = [Sale Date]
    in
    Table.SelectRows(#"Exchange Rate", each [Fiscal Start Date] <= outerDate and  [Fiscal End Date] >= outerDate)

What happens is the following the value of the column {Sale Date] is stored to the variable outer date. This variable is then passed to the function Table.SelectRows(...) The usage of a variable is necessary to pass the variable into a function that references a different table.

Then you should see something similar like this

Expand the table and just select the column "Exchange Rate", rename it accordingly and there you go (hopefully)

Regards