Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rachaelnelson
Frequent Visitor

M script for Custom Column

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.

 

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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

variable in m.png

 

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

 

Regards



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

1 REPLY 1
TomMartens
Super User
Super User

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

variable in m.png

 

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

 

Regards



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.