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.
You'll probably get the errors are probably where there is no match with the other table?
Either replace it with the value from the other column or write a conditional statement that uses that column if the other operatoin failed.
Thank you very much ImkeF, that worked! I used try...otherwise to fix it. I implemented it now in my original data model also and as expected, the processing takes much longer now. My data model is in AAS actually, and I connect to it using Direct Query. So I am wondering at which level my performance will be affected. Because we are processsing the AAS every hour right now, so will this code in power query only make the automatic processing longer? Or will it also have an effect on the rendering of the reports in PowerBI for the end user that is using this generated column? Haven't tested it yet, so I'll get to that soon after.
I am also looking into the partitioning option, and it's a bit too advanced for me, so I need to study it a bit to understand how to implement in my case and how that affects my model.