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

In this article, we’ll walk through how to retrieve a corresponding value for a given date that falls within a specified date range 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 of this process is to calculate the total pay for each person on each date by multiplying the hours worked by the applicable rate for that day. We will use two data tables 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, we’ll 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 ribbon in Excel, click Get Data then From Other Sources and select Blank Query.
2. The Power Query Editor window opens.
3. From the Home ribbon 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 ribbon in Power Query, click New Source then Other Sources and select Blank Query.
2. From the Home ribbon 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 ribbon in Power Query, click New Source then Other Sources and select Blank Query.
2. From the Home ribbon 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.

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

The result is a new table that displays the correct rate and daily pay for each person, based on their hours worked and the applicable rate for that date range. This process ensures that the right rate is applied, and the total pay is accurately calculated.

Summary

This guide demonstrated how to use Power Query to retrieve values based on a date range and perform calculations based on those values. By following the steps outlined, you can easily apply this technique to various scenarios based on ranges.