cancel
Showing results for 
Search instead for 
Did you mean: 
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

Accepted Solutions
Super User III
Super User III

Re: M script for Custom Column

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

View solution in original post

1 REPLY 1
Super User III
Super User III

Re: M script for Custom Column

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors