Power Query: Merge Tables by Date Range

Key insights

• Merge on Dates Between: Join rows when a date in one table falls between a FromDate and ToDate in another table.
  Use this to connect transactions to records that are valid for a time range, such as matching sales to customer records active during the sale.
• Equality Join Limitation: Power Query’s built-in Merge supports only exact equality joins on column values.
  Because “between” comparisons are not native, you must implement custom logic to get a date-range join.
• Custom Function: Write a simple M function that checks if a date falls between two dates and apply it with a custom column or Table.SelectRows.
  This method scales better than expanding every date and lets you combine date checks with other key filters like CustomerID.
• Date List Expansion: You can expand each range into a list of dates and then merge on exact dates.
  That approach works but often becomes very slow and memory-heavy for large ranges or many rows.
• Performance Tips: Filter rows early, limit columns before merges, and avoid generating full date lists when possible.
  Prefer function-based conditional joins, add key filters (IDs) to reduce comparisons, and test performance on sample data first.
• Best Practices & Use Cases: Use function-based merges for historical lookups, rate changes, or time-valid lookups in Power BI and Fabric.
  Leverage community templates and sample M snippets, adapt them to your keys and scale, and measure performance before production deployment.

Overview of the YouTube video

The recent you_tube_video by Reza Rad (RADACAD) [MVP] explains how to perform a Dates Between style merge in Power Query and Power BI, turning a common blog walkthrough into a visual tutorial. The presenter outlines why standard merge operations, which rely on equality, fall short when you need to link rows based on whether a date falls inside a range. Therefore, he guides viewers through practical alternatives and demos using sample data drawn from his blog resources. Overall, the video aims to help analysts apply temporal logic directly inside Power Query without leaving the Power BI environment.

Techniques demonstrated

First, Reza shows a direct approach using a custom function that checks if a single date lies between a FromDate and ToDate in the reference table, then applies that function across rows. Next, he compares this with an expansion method that creates explicit date lists for each range and then performs equality merges against those expanded lists. In addition, the tutorial covers how to combine these date conditions with other keys like CustomerID to avoid incorrect matches. He emphasizes how to use the M language to implement conditional logic and streamline the merge steps.

Performance trade-offs and challenges

Reza explains that expanding ranges into full date lists is straightforward to understand but can become very slow and memory-intensive with large datasets. Conversely, the custom function approach avoids large expansions but can still be costly if implemented in a naive, row-by-row manner, since each check may scan the reference table repeatedly. Consequently, the video highlights the need to balance clarity and performance, recommending smarter filters and pre-joins to reduce the number of checks. He also notes that the choice often depends on dataset size, available memory, and refresh frequency.

Practical tips and optimizations

To improve efficiency, the presenter suggests filtering both tables to a relevant subset before applying the more expensive row-level logic, thereby shrinking the search space. He also recommends combining date-range checks with exact-key matches early in the query to avoid unnecessary comparisons, which often yields significant speedups. Furthermore, Reza points out that reusing intermediate tables and leveraging built-in table functions can cut down on repeated calculations. In short, preprocessing and thoughtful ordering of steps make the difference between a workable solution and one that times out.

Comparing approaches and recommended use cases

Reza contrasts the list-expansion approach as suitable for small to medium datasets or one-off ad hoc analyses, while the function-based conditional merge scales better when you design it to minimize full-table scans. For example, the list method is easy to implement and debug, but it quickly runs into performance barriers when ranges span many dates. By contrast, the function approach is more flexible and can be adapted to multiple conditions, yet it demands careful coding to avoid repeated heavy operations. Therefore, he recommends testing both strategies on a sample of your data to see which strikes the best balance between speed and complexity.

Implications for Power BI workflows

The video concludes by exploring how temporal joins impact report refresh times and maintainability, urging developers to document custom functions and to keep transformations transparent for future troubleshooting. In addition, Reza underscores that while Power Query can handle many complex joins, sometimes moving heavy-duty operations to a data warehouse or an ETL tool is justified when datasets grow too large. Consequently, teams should consider operational factors such as scheduled refresh windows, memory limits, and developer skill when choosing to implement these merges inside Power BI.

Final takeaways

Overall, the you_tube_video by Reza Rad delivers a clear, practical guide to implementing Dates Between merges in Power Query, showing both straightforward and advanced methods while honestly addressing their limitations. He balances step-by-step demonstrations with performance advice and encourages viewers to adapt the patterns to their own data. For readers and analysts, the main lesson is to weigh simplicity against scalability and to test approaches on representative data before deploying them in production. Finally, the video complements his written blog content by offering a hands-on walkthrough that accelerates learning for Power BI professionals.

Keywords

Power Query merge by date, Power BI merge tables by date range, merge tables between dates Power Query, join tables on date range Power BI, Power Query date range merge, merge queries by date range Power BI, merge tables with date between condition Power Query, Power BI join between dates