Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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
User | Count |
---|---|
124 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |