Search code examples
powerbipowerquerym

Reduce the time taken to run this query looking updates from another table

I have a table of the following format containing a "Period End Date" for each month. There are multiple sets of dates in this table for each different "Label".

enter image description here

I have a second table which contains "Label" and "Date" columns. For each date in this table I want to lookup the month that it falls before in Table 1. I.e. the period end date that immediately proceeds the date.

enter image description here

I have the following code to generate the "Month" column, which appears to be working as expected but the run time is >15 minutes. Looking for suggestions on how to make more efficient.

= Table.AddColumn(Table2,"Month",(x)=>Table.SelectRows(Table1, each x[Date] <= [Period End Date] and x[Label] = [Label])[Month]{0})
Solution

  • Buffer the table.

    e.g.

    bufferedTable = Table.Buffer(Table1),
yourStep = Table.AddColumn(Table2,"Month",(x)=>Table.SelectRows(bufferedTable , each x[Date] <= [Period End Date] and x[Label] = [Label])[Month]{0})