Forum Discussion
M Script Help - custom column referencing another table
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.
ImkeF, Since my M skills are not yet at that level so I can implement the partitioning option, I decided to implement the solution at a different level. We actually have full control over the SQL database, so we actually created a separate view of the table where we added the extra column by following the logic I detailed. So we used SQL to fix this, and I guess the performance has improved a lot, since creating that view takes just seconds.
In your experience, if I do have the option to fix such issues at SQL database level, is it preffered to do it there rather than in M?
There is a clear answer to it:
Cleaning at the data source beats cleaning in Power BI ... always ;)