Power Query: Get the Corresponding Value for a Date Within a Date Range

This article demonstrates how to retrieve the value associated with the date range containing a given date using Power Query. While this example focuses on retrieving hourly rates and calculating daily pay, the same approach can be applied to other scenarios such as determining time-based sales, employee salaries, or discount rates based on date ranges.

Instructions

Step 1: Create Underlying Data

The goal is to calculate the total pay for each person on each date by multiplying the hours worked by the applicable rate for that day. Two data tables are used for this task.

The first table (TBL_HOURS) contains the hours worked by each person on a particular date.

The second table (TBL_RATES) lists the valid hourly rate for each person over a specific date range.

Step 2: Load Data in Power Query

Next, create two queries in Power Query to load the data. Both queries should be set as Connection Only.

Load TBL_HOURS

1. From the Data tab in Excel, click Get Data then From Other Sources and select Blank Query.
2. The Power Query Editor window opens.
3. From the Home tab in Power Query, click Advanced Editor.
4. The Advanced Editor window opens.
5. Paste the following code into the Advanced Editor window to retrieve the TBL_HOURS data.
6. Click the Done button.
7. In the Query Settings pane under the Properties section, rename the query to QRY_TBL_HOURS.

let
  Source = Excel.CurrentWorkbook(){[Name = "TBL_HOURS"]}[Content],
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {
      {"NAME", type text},
      {"DATE", type date},
      {"HOURS", type number}
    }
  )
in
  #"Changed Type"

Load TBL_RATES

1. From the Home tab in Power Query, click New Source then Other Sources and select Blank Query.
2. From the Home tab in Power Query, click Advanced Editor.
3. The Advanced Editor window opens.
4. Paste the following code into the Advanced Editor window to retrieve the TBL_RATES data.
5. Click the Done button.
6. In the Query Settings pane under the Properties section, rename the query to QRY_TBL_RATES.

let
  Source = Excel.CurrentWorkbook(){[Name = "TBL_RATES"]}[Content],
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {
      {"NAME", type text},
      {"START DATE", type date},
      {"END DATE", type date},
      {"RATE", Currency.Type}
    }
  )
in
  #"Changed Type"

Step 3: Create Lookup Query

Now, create a lookup query to match the data from TBL_HOURS with the correct rate from TBL_RATES.

1. From the Home tab in Power Query, click New Source then Other Sources and select Blank Query.
2. From the Home tab in Power Query, click Advanced Editor.
3. The Advanced Editor window opens.
4. Paste the following code into the Advanced Editor window.
5. Click the Done button.
6. In the Query Settings pane under the Properties section, rename the query to QRY_TBL_SPEND.
7. Close the Power Query Editor and load QRY_TBL_SPEND to a table in a new worksheet.

let
  Source_QRY_TBL_HOURS = QRY_TBL_HOURS,
  Source_QRY_TBL_RATES = QRY_TBL_RATES,
  #"Added Column RATE" = Table.AddColumn(
    Source_QRY_TBL_HOURS,
    "RATE",
    each Table.SelectRows(
      Source_QRY_TBL_RATES,
      (rates) =>
        rates[NAME] = [NAME] and ([DATE] >= rates[START DATE] and [DATE] <= rates[END DATE])
    )[RATE]{0},
    Currency.Type
  ),
  #"Added Column DAILY PAY" = Table.AddColumn(
    #"Added Column RATE",
    "DAILY PAY",
    each [HOURS] * [RATE],
    Currency.Type
  )
in
  #"Added Column DAILY PAY"

NOTE: If there is a chance the query will not find a matching rate, change [RATE]{0} to [RATE]{0}?. Adding the ? makes the query return null instead of an error when no corresponding value is found. This approach evaluates Table.SelectRows once for every row in Source_QRY_TBL_HOURS, comparing it against the full Source_QRY_TBL_RATES table each time. For larger datasets, expanding TBL_RATES into one row per covered date and merging on NAME and DATE performs significantly better than the nested-filter method shown here.

Query Explanation

1. The query loads the QRY_TBL_HOURS and QRY_TBL_RATES tables.
2. A new custom column named RATE is added to Source_QRY_TBL_HOURS. This step retrieves the applicable rate by comparing the person’s name and date in Source_QRY_TBL_HOURS to the valid date range in Source_QRY_TBL_RATES. If a match is found, the corresponding rate is returned.
3. Another custom column is added to calculate the daily pay by multiplying the hours worked by the corresponding rate.

Results

Each record now includes the applicable hourly rate and calculated daily pay based on the employee’s effective rate during that period. This process ensures that the right rate is applied, and the total pay is accurately calculated.

Summary

This article demonstrates how to use Power Query to retrieve values based on a date range and perform calculations based on those values. This same approach can be adapted whenever records must be matched against effective-date ranges, including pricing schedules, tax brackets, benefit plans, subscription periods, and other time-based lookup scenarios.