Forum Discussion
M Script Help - custom column referencing another table
I have the following situation that I would like to fix with power query. I have a sales fact table and a department dimension. In the sales table, I have a seller column, where each seller belongs to a default department. But sometimes, during a certain time period, the seller can temporarily switch departments.
So, in the Sales table I would like to create a 'Current Department' column that calculates the current department based on the invoice date:
if sales.[Invoice Date] >= Department.[FromDate] and sales.[Invoice Date] <= Department.[ToDate] then Department.[Department] else sales.[DepartmentDefault]
This is the result
Help? I tried to use a merge with left outer join between those two tables, but I 'duplicate' the rows in sales, to include the different periods for the same seller, so then the values I have in other columns (like sales value, quantity, margin) get duplicated as well and the totals for time periods get messed up.
You can add a column in sales with this formula:
Table.SelectRows(Department,
(Dept) => (Dept[FromDate] < [Invoice Date]) and
(Dept[ToDate] >= [Invoice Date]) and
(Dept[Seller] = [Seller])
)[Department]{0}This will lookup the desired value from te Department-table (and if it is null, replace it with the value of your current row).
But it might be slow. To speed it up you can "partition" your Sales table by grouping it on Seller, merge with Department on seller and apply the above selection-formula in the partitioned fields: Just omit the "(Dept[Seller] = [Seller])"-part then.
Another alternative is to create an intermediate table that you dont load to the data model where you expand the dates of the time intervals of your Department-table so that every day will have one row. Then you can simply merge that new date-column with your Sales-table.
9 Replies
Invoking ImkeF
You can add a column in sales with this formula:
Table.SelectRows(Department,
(Dept) => (Dept[FromDate] < [Invoice Date]) and
(Dept[ToDate] >= [Invoice Date]) and
(Dept[Seller] = [Seller])
)[Department]{0}This will lookup the desired value from te Department-table (and if it is null, replace it with the value of your current row).
But it might be slow. To speed it up you can "partition" your Sales table by grouping it on Seller, merge with Department on seller and apply the above selection-formula in the partitioned fields: Just omit the "(Dept[Seller] = [Seller])"-part then.
Another alternative is to create an intermediate table that you dont load to the data model where you expand the dates of the time intervals of your Department-table so that every day will have one row. Then you can simply merge that new date-column with your Sales-table.
and a link to an article I've written about "partitioning": https://wp.me/p6lgsG-yh
