Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Within Power Query, I have merged two tables based on a date range. I followed the instructions within this article. https://exceed.hr/blog/merging-with-date-range-using-power-query/
It's working as expected, but it takes a long time to refresh. I have also implemented Table.Buffer, but refresh times are still the same. Do any Mquery experts have some suggestions?
Solved! Go to Solution.
First thing I notice is that you are not referencing your buffered table in the function. Try this instead.
#"Changed column type 3" = Table.TransformColumnTypes(#"Filtered Rows", {{"AvailableHours", type number}}),
BufferedTable = Table.Buffer(#"EmployeeData"),
#"Added Custom99" = Table.AddColumn(#"Changed column type 3", "Custom99",
(S) => Table.SelectRows(BufferedTable, (P)=> S[Date] >= P[ValidFrom] and S[Date] <= P[ValidTo] and P[CompanyID] = S[CompanyID])),
#"Expanded Custom99" = Table.ExpandTableColumn(#"Added Custom99", "Custom99", {"EmployeeID", "CostCenterID", "FTE"}, {"EmployeeID", "CostCenterID", "FTE"}),
Pat
Hi @PoweredOut ,
I normally recommend strongly against using DAX calculated columns, but this is the one exception I've ever made to date. I actually recommend strongly in favour of doing conditional merges in DAX instead of M.
Try your query with the correctly-referenced buffered table(s) as @ppm1 suggests and, if the performance is still poor, then try a DAX calculated column in your EmployeeData table like this:
Custom99 =
CALCULATE(
VAR __dateRow = VALUES(EmployeeData[Date])
VAR __companyRow = VALUES(EmployeeData[CompanyID])
RETURN
MAXX(
FILTER(
OtherTable,
OtherTable[CompanyID] = __companyRow
&& OtherTable[ValidFrom] <= __dateRow
&& OtherTable[ValidTo] >= __dateRow
),
OtherTable[EmployeeID]
)
)
You'll have to do it again for each [CostCenterID] and [FTE] but, even doing this three times, I'll bet it's still orders of magnitude faster than doing it in Power Query/M.
Pete
Proud to be a Datanaut!
Hi @PoweredOut ,
I normally recommend strongly against using DAX calculated columns, but this is the one exception I've ever made to date. I actually recommend strongly in favour of doing conditional merges in DAX instead of M.
Try your query with the correctly-referenced buffered table(s) as @ppm1 suggests and, if the performance is still poor, then try a DAX calculated column in your EmployeeData table like this:
Custom99 =
CALCULATE(
VAR __dateRow = VALUES(EmployeeData[Date])
VAR __companyRow = VALUES(EmployeeData[CompanyID])
RETURN
MAXX(
FILTER(
OtherTable,
OtherTable[CompanyID] = __companyRow
&& OtherTable[ValidFrom] <= __dateRow
&& OtherTable[ValidTo] >= __dateRow
),
OtherTable[EmployeeID]
)
)
You'll have to do it again for each [CostCenterID] and [FTE] but, even doing this three times, I'll bet it's still orders of magnitude faster than doing it in Power Query/M.
Pete
Proud to be a Datanaut!
Hi Pete
Thanks for your resolution, Pat's solution worked for me.
Joe
Please share your M code for both queries from the Advanced Editor to get specific suggestions. Also how many rows in each table? And how long is it taking?
Pat
Good morning Pat
I'm merging table EmployeeData which has around 1000 Rows (This will grow over time, but nothing major) with a AvailableDays table which has 100K+ (Will also grow, again nothing major)
Thanks
Joe
#"Changed column type 3" = Table.TransformColumnTypes(#"Filtered Rows", {{"AvailableHours", type number}}),
BufferedTable = Table.Buffer(#"EmployeData"),
#"Added Custom99" = Table.AddColumn(#"Changed column type 3", "Custom99",
(S) => Table.SelectRows(#"EmployeeData", (P)=> S[Date] >= P[ValidFrom] and S[Date] <= P[ValidTo] and P[CompanyID] = S[CompanyID])),
#"Expanded Custom99" = Table.ExpandTableColumn(#"Added Custom99", "Custom99", {"EmployeeID", "CostCenterID", "FTE"}, {"EmployeeID", "CostCenterID", "FTE"}),
First thing I notice is that you are not referencing your buffered table in the function. Try this instead.
#"Changed column type 3" = Table.TransformColumnTypes(#"Filtered Rows", {{"AvailableHours", type number}}),
BufferedTable = Table.Buffer(#"EmployeeData"),
#"Added Custom99" = Table.AddColumn(#"Changed column type 3", "Custom99",
(S) => Table.SelectRows(BufferedTable, (P)=> S[Date] >= P[ValidFrom] and S[Date] <= P[ValidTo] and P[CompanyID] = S[CompanyID])),
#"Expanded Custom99" = Table.ExpandTableColumn(#"Added Custom99", "Custom99", {"EmployeeID", "CostCenterID", "FTE"}, {"EmployeeID", "CostCenterID", "FTE"}),
Pat
Thank you Pat
It was the table reference that was the issue. The table refreshed in seconds.
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
13 | |
12 | |
11 | |
8 |
User | Count |
---|---|
43 | |
26 | |
16 | |
15 | |
12 |