Forum Discussion
Power Query - Join/Merge based on Date between 2 dates
Hi there,
there is actually a pattern for this task that will probably increase speed: https://www.skillwave.training/vlookup-true-in-power-query/
Also see attached file.
It delivers correct results if the intervals are without gaps like in the data pedanticpad gave in his initial thread.
edhans that worked, but my refresh time for that table has gone from 30 seconds to nearly half an hour. Is there another option?
Unfortuntely there is no merge between ranges in Power Query, so my code is doing a lot of comparisons row by row and a Table.SelectRows() for every single item.
Another possiblility is to do a cartesian join. See if this helps. Not optimistic.
#"Added Custom1" =
Table.AddColumn(
#"Added Custom",
"Some Number Again",
each let
varDate = [Sales Date]
in
Table.SelectRows([Custom], each varDate >= [Date1] and varDate <= [Date2])[Some Number]{0}
)Go grab my file again using the link above. It has been updated with a Sales2 table to show you the entire process.
Someone may be able to write more efficient code. Not sure if ImkeF could improve this or not - but if anyone can, it is her.