Excel Lookup Using Date Ranges and Multiple Criteria in Power Query

Some data analyses are best solved with a proper database and a simple SQL query. In my opinion, this is one of those examples. However, such conveniences are not always available.

In this scenario, we have a table containing the number of hours each person worked on a particular date. In a second table, we have the hourly rate that is valid for a particular period of time for each person. We want to calculate the total cost for each person on each date using the hours worked and the valid effective rate for that particular date.

While this example focuses on hourly rates, the concept is applicable to countless scenarios such as looking up prices for time-based sales or discounts, employee salaries, document revisions, or data that has a defined lifespan.

In the expected result, we find the rate varies based on the person and the date to calculate the correct amount.

Excel – Expected Result with Hours, Rate, and Amount
Excel – Expected Result with Hours, Rate, and Amount

The Data

As I described earlier, we have two data sets in Excel. The first data set includes the hours worked on a particular date by a particular person. I’ll refer to the first data set as the Hours table. The second data set includes the valid rate for a particular person over an effective period defined by a start and end date. I’ll refer to the second data set as the Rate table.

Excel – Hours Table
Excel – Hours Table
Excel – Rate Table
Excel – Rate Table

Method 1: Excel Array Formula

Before we get into the Power Query solution, I’ll provide a solution using an array formula (or CSE formula). This is certainly easier than adding the overhead and complexity needed for Power Query. However, I find the formula to be more difficult to read and maintain for the average Excel user. I described a similar solution in an earlier post titled Excel Lookup With Multiple Criteria Using SUMPRODUCT or Array Formula. If you decide to use this formula, make sure you press Ctrl + Shift + Enter to confirm the formula.

This formula assumes the Hours data set is on a worksheet named HOURS and the Rates data set is on a worksheet named RATES.

=INDEX(RATES!$D$2:$D$5,MATCH(1,IF(HOURS!$B2>=RATES!$B$2:$B$5,IF(HOURS!$B2<=RATES!$C$2:$C$5,IF(HOURS!$A2=RATES!$A$2:$A$5,1))),0))

The following is another version of the above array formula with a more simplified but potentially less readable structure.

=INDEX(RATES!$D$2:$D$5,MATCH(1,(HOURS!$B2>=RATES!$B$2:$B$5)*(HOURS!$B2<=RATES!$C$2:$C$5)*(HOURS!$A2=RATES!$A$2:$A$5),0))

Method 2: Power Query

This method uses Power Query to lookup the correct rate. The heart of this solution is a straightforward merge query with a date filter.

Method 2: Step 1 – Define Named Ranges

In this step, the Hours table and Rates table are given defined names. In Excel, on the Formulas ribbon click Name Manager. Click New… and create a new entry named TBL_HOURS as follows. Click OK when complete.

Excel – Name Manager (TBL_HOURS)
Excel – Name Manager (TBL_HOURS)

Now define a name for the Rates table. Click New… and create a new entry named TBL_RATES as follows. Click OK when complete. Click Close to exit the Name Manager.

Excel – Name Manager (TBL_RATES)
Excel – Name Manager (TBL_RATES)

Method 2: Step 2 – Get Data Into Power Query

On the Data ribbon in Excel, click Get Data and select From Other Sources and then Blank Query. The Power Query Editor opens. From the Home ribbon click Advanced Editor and add the following code. Click Done. In the Query Settings pane under Properties change the query name to PQ_TBL_HOURS. This code brings the data found in the range with defined name TBL_HOURS into Power Query. This query should be set as a Connection Only query.

let
    Source = Excel.CurrentWorkbook(){[Name="TBL_HOURS"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([NAME] <> null)),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"NAME", type text}, {"DATE", type date}, {"HOURS", type number}})
in
    #"Changed Type"

Next we’ll bring the TBL_RATES data into Power Query. Remaining in the Power Query Editor, from the Home ribbon click Advanced Editor and add the following code. Click Done. In the Query Settings pane under Properties, change the query name to PQ_TBL_RATES. This code brings the data found in the range with defined name TBL_RATES into Power Query. This query should be set as a Connection Only query.

let
    Source = Excel.CurrentWorkbook(){[Name="TBL_RATES"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([NAME] <> null)),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"NAME", type text}, {"EFFECTIVE START DATE", type date}, {"EFFECTIVE END DATE", type date}, {"RATE", Currency.Type}})
in
    #"Changed Type"

Method 2: Step 3 – Lookup Query

Now we create the lookup query. Remaining in the Power Query Editor, from the Home ribbon click Advanced Editor and add the following code. Click Done. In the Query Settings pane under Properties, change the query name to PQ_TBL_SPEND. Close the Power Query Editor and load PQ_TBL_SPEND to a Table in a New worksheet.

let
    Source = Table.NestedJoin(PQ_TBL_HOURS, {"NAME"}, PQ_TBL_RATES, {"NAME"}, "PQ_TBL_RATES", JoinKind.LeftOuter),
    #"Expanded PQ_TBL_RATES" = Table.ExpandTableColumn(Source, "PQ_TBL_RATES", {"NAME", "EFFECTIVE START DATE", "EFFECTIVE END DATE", "RATE"}, {"PQ_TBL_RATES.NAME", "PQ_TBL_RATES.EFFECTIVE START DATE", "PQ_TBL_RATES.EFFECTIVE END DATE", "PQ_TBL_RATES.RATE"}),
    #"Filtered Rates" = Table.SelectRows(#"Expanded PQ_TBL_RATES", each [DATE] >= [PQ_TBL_RATES.EFFECTIVE START DATE] and [DATE] <= [PQ_TBL_RATES.EFFECTIVE END DATE]),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rates",{{"PQ_TBL_RATES.RATE", "RATE"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "AMOUNT", each [HOURS]*[RATE]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"PQ_TBL_RATES.NAME", "PQ_TBL_RATES.EFFECTIVE START DATE", "PQ_TBL_RATES.EFFECTIVE END DATE"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"AMOUNT", Currency.Type}})
in
    #"Changed Type"

The first step in the query performs a Merge between PQ_TBL_HOURS and PQ_TBL_RATES based on the common NAME column. The matching PQ_TBL_RATES entries are added as a column of tables.

Power Query – Merge Query
Power Query – Merge Query

The second step expands the PQ_TBL_RATES column table. For each row in TBL_HOURS and TBL_RATES where the NAME column matched we find corresponding expanded rows. This creates entries where PQ_TBL_HOURS data is duplicated for each date based on the number of entries in PQ_TBL_RATES.

Power Query – Expanded
Power Query – Expanded

To eliminate the rows where the DATE column from TBL_HOURS does not exist between PQ_TBL_RATES.EFFECTIVE START DATE and PQ_TBL_RATES.EFFECTIVE END DATE we apply a filter using SelectRows and the condition [DATE] >= [PQ_TBL_RATES.EFFECTIVE START DATE] and [DATE] <= [PQ_TBL_RATES.EFFECTIVE END DATE]. We find the correct rates are now applied for the particular person and particular date.

Power Query – Filtered Rows
Power Query – Filtered Rows

Rename column PQ_TBL_RATES.RATE to RATE.

Power Query – Renamed Columns
Power Query – Renamed Columns

Add a new column to calculate AMOUNT which is defined as HOURS multiplied by RATE.

Power Query – Add Column
Power Query – Add Column

We no longer need the PQ_TBL_RATES.NAME, PQ_TBL_RATES.EFFECTIVE START DATE, and PQ_TBL_RATES.EFFECTIVE END DATE columns so they are removed.

Power Query – Remove Columns
Power Query – Remove Columns

The final step is to clean up the data types. AMOUNT is changed to the Currency data type.

Power Query – Changed Type
Power Query – Changed Type

Result

The calculated table is loaded to a table on a new worksheet. We see the correct effective rates are applied and the resulting amount is correct.

Excel – Hours, Rate, Amount Table
Excel – Hours, Rate, Amount Table

Display Excel File Modified Date in Power BI

In this example, I will demonstrate two methods to display the data last updated date in Power BI. This is one of those pesky activities that is more complicated than expected. Both methods use an Excel file as the underlying data source for the Power BI report although the second method can use any file type. To be clear, these methods are not simply using the Power BI timestamp from when the report was published or when a scheduled automatic refresh occurred. These methods either retrieve the last save time within Excel itself or use the last modified timestamp from the file system.

Method 1: Excel VBA

Let’s start with the first method which uses a custom user-defined VBA function in Excel to retrieve the “Last Save Time” property.

Method 1: Step 1 – Create User-Defined Function (UDF) to Retrieve Last Save Time Property Value

On the Developer ribbon in Excel, click Visual Basic. The Microsoft Visual Basic for Applications window opens. On the Insert menu, click Module. A new module is added to the VBAProject. In this example, the new module is named Module1. Open Module1 and add the following code. This code creates a custom function to retrieve the timestamp from the last save time.

Option Explicit

Function Last_Saved_Timestamp() As Date
  On Error GoTo errorHandler

  With Application
    .ScreenUpdating = False
    .EnableEvents = False
  End With

  Last_Saved_Timestamp = ThisWorkbook.BuiltinDocumentProperties("Last Save Time")

errorHandler:
  With Application
    .ScreenUpdating = True
    .EnableEvents = True
  End With

  If Err.Number <> 0 Then
    MsgBox Err.Description
  End If
End Function
Excel VBA – Get Last Saved Timestamp Function
Excel VBA – Get Last Saved Timestamp Function

Method 1: Step 2 – Force Full Recalculation Before Save

Remaining in Microsoft Visual Basic for Applications, open ThisWorkbook and add the following code. This code forces a recalculation of the workbook and updates the cell value containing the last saved timestamp (which will be added in a later step) before the file is saved.

Close the Microsoft Visual Basic for Applications window.

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  On Error GoTo errorHandler

  With Application
    .ScreenUpdating = False
    .EnableEvents = False
  End With
  
  Application.CalculateFull

errorHandler:
  With Application
    .ScreenUpdating = True
    .EnableEvents = True
  End With

  If Err.Number <> 0 Then
    MsgBox Err.Description
  End If
End Sub
Excel VBA – Recalculate Before Save
Excel VBA – Recalculate Before Save

Method 1: Step 3 – Retrieve Last Save Time

After returning to Excel, insert a new worksheet. I have renamed the worksheet as EXAMPLE. In cell A1, enter the value “LAST SAVED DATE“. In cell A2, use the newly created VBA function by entering the formula “=Last_Saved_Timestamp()“. The timestamp will be displayed as a serial number.

Save the file and close Excel.

Excel – Using Custom VBA Function
Excel – Using Custom VBA Function

Method 1: Step 4 – Load Data in Power BI

In Power BI Desktop, on the Home ribbon click Get data and select Excel workbook. Select the Excel file created in the earlier steps. The Navigator window opens. Check EXAMPLE and click the Load button. In the Fields pane, the EXAMPLE table and the LAST SAVED DATE field are available.

Power BI Desktop – Get Data
Power BI Desktop – Get Data

If LAST SAVED DATE is summarized, make sure you turn summarization off for that field. In the Fields pane, click LAST SAVED DATE. On the Column tools ribbon, select Don’t summarize in the Summarization drop-down.

Also on the Column tools ribbon, change the data type for LAST SAVED DATE. In the Data type drop-down, select Date. The Data type change dialog is displayed. Click the OK button. In the Format drop-down, select the desired data format to use in the report.

Power BI Desktop – Last Updated Date Configuration
Power BI Desktop – Last Updated Date Configuration

Method 1: Step 5 – Display Data Last Updated Date Using a Card Visualization

Switch to the Report view. Add a Card visualization. In the Fields section, add LAST SAVED DATE from EXAMPLE. If the field defaults to Earliest change it to Latest. This step most likely isn’t necessary if there is only one entry in the table (as there should be only one entry), but I still change it to Latest just in case.

Power BI Desktop – Card Visual Configuration
Power BI Desktop – Card Visual Configuration

In the Format visual section, turn off Category label. Adjust the font size and any other attributes as needed.

Power BI Desktop – Card Visual Formatting
Power BI Desktop – Card Visual Formatting

Method 1: Result

As expected, the report now displays the date when the underlying Excel file was last saved. The next time the Power BI report is refreshed, the timestamp will be retrieved from Excel and the Card visualization will reflect the latest date.

Power BI Desktop – Last Updated Date
Power BI Desktop – Last Updated Date

Method 2: Power Query File Properties

The alternative method uses Power Query to retrieve the Last Modified Date from the file system properties for the Excel file. As I mentioned earlier, this method is not limited to Excel and it will work with any file type.

Method 2: Step 1 – Get Data

In Power BI Desktop, on the Home ribbon click Get data and select More…. The Get Data dialog box opens. Select File and then Folder. Click the Connect button.

Power BI Desktop – Get Data From Folder
Power BI Desktop – Get Data From Folder

Method 2: Step 2 – Select Path

The Folder dialog box is displayed. Enter or browse to the direct parent folder of the data file. In this example, the data file is stored in the test folder. Click The OK button when finished.

Power BI Desktop – Get Folder Path
Power BI Desktop – Get Folder Path

Method 2: Step 3 – Transform Data

Another dialog box opens displaying a list of files and their attributes from the selected folder. Since the Power BI report uses the example.xlsm file we’ll need to reduce this list to that individual entry and only return the Date modified attribute. Click the Transform Data button.

Power BI Desktop – Folder Data
Power BI Desktop – Folder Data

Method 2: Step 4 – Edit Query

The Power Query window opens. The first step in the query is labeled Source and it returns the file list from the previous screen. Verify that the list is correct.

Power Query – Source Folder Data
Power Query – Source Folder Data

Method 2: Step 5 – Filter List

Apply a filter to the Name column so that the file used by the Power BI report is the only row returned.

Power Query – Filter File Name
Power Query – Filter File Name

Method 2: Step 6 – Remove Columns

With the exception of the Date modified column, remove all of the other columns from the list.

Power Query – Remove Columns
Power Query – Remove Columns

Method 2: Step 7 – Rename Column

Rename the Date modified column to LAST SAVED DATE. This is the same field name we used in the other method so this step is needed only to maintain consistency.

Power Query – Rename Column
Power Query – Rename Column

Method 2: Step 8 – Final Query Code

The following is the final query code after applying all filters and operations. No action needed in this step other than to verify that the query is correct.

let
    Source = Folder.Files("C:\test\"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "example.xlsm")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Content", "Name", "Extension", "Date accessed", "Date created", "Attributes", "Folder Path"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Date modified", "LAST SAVED DATE"}})
in
    #"Renamed Columns"

Method 2: Step 9 – Rename Query

In the Query Settings, rename the query to FOLDER EXAMPLE or something more descriptive and relevant for the project. The query name is what will be displayed in the Fields pane in Power BI.

Power Query – Rename Query
Power Query – Rename Query

Method 2: Result

Add another Card visualization. Switch to the Report view. Add a Card visualization. In the Fields section, add LAST SAVED DATE from FOLDER EXAMPLE. If the field defaults to Earliest change it to Latest.

In the following screenshot, we can see that both methods produce the same expected result.

Power BI Desktop – Result Using Folder Data Method
Power BI Desktop – Result Using Folder Data Method

Custom Power BI Date Tables Using Power Query

By default, Power BI creates a date hierarchy based on the date data it identifies in the data model. This default hierarchy may be sufficient in many instances. However, you may quickly find it produces unexpected results in some visualizations. Custom date tables in Power BI provide fine-tuned control over date hierarchies. By creating a custom date table, you have complete control over the date hierarchy behavior as well as the calculation and formatting of various date dimensions.

Step 1 – Create Date Table Using Power Query

In this example, Power Query is used to generate the date table. I chose Power Query out of personal preference, but the date table may also be created using a DAX expression in Power BI.

On the Home ribbon, click Get data and select Blank query. The Power Query Editor opens to a blank query. On the Home ribbon in the Power Query Editor, click Advanced Editor and add the following code. Adjust the StartDate and EndDate values to satisfy the minimum and maximum date range needed in your specific report. Click Done to close the Advanced Editor. After returning to the Power Query Editor, rename the table in the Query Settings pane under the PROPERTIES section. I use the name TBL_PQ_DATE_TABLE.

let
    StartDate = #date(2022, 1, 1),
    EndDate = #date(2022, 12, 31),
    DateList = List.Dates(StartDate, Number.From(EndDate) - Number.From(StartDate) + 1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "DATE"}}),
    #"Add Column YEAR" = Table.AddColumn(#"Renamed Columns", "YEAR", each Date.Year([DATE])),
    #"Add Column QUARTER" = Table.AddColumn(#"Add Column YEAR", "QUARTER", each Date.QuarterOfYear([DATE])),
    #"Add Column MONTH" = Table.AddColumn(#"Add Column QUARTER", "MONTH", each Date.Month([DATE])),
    #"Add Column DAY" = Table.AddColumn(#"Add Column MONTH", "DAY", each Date.Day([DATE])),
    #"Add Column QUARTER_LABEL" = Table.AddColumn(#"Add Column DAY", "QUARTER_LABEL", each "Q" & Number.ToText([QUARTER])),
    #"Add Column MONTH_LABEL" = Table.AddColumn(#"Add Column QUARTER_LABEL", "MONTH_LABEL", each Date.ToText([DATE],"MMM")),
    #"Add Column DAY_LABEL" = Table.AddColumn(#"Add Column MONTH_LABEL", "DAY_LABEL", each Date.ToText([DATE],"ddd")),
    #"Add Column DAY_OF_WEEK" = Table.AddColumn(#"Add Column DAY_LABEL", "DAY_OF_WEEK", each Date.DayOfWeek([DATE])),
    #"Add Column YEAR_QUARTER_LABEL" = Table.AddColumn(#"Add Column DAY_OF_WEEK", "YEAR_QUARTER_LABEL", each Number.ToText([YEAR]) & " " & [QUARTER_LABEL]),
    #"Add Column YEAR_MONTH_LABEL" = Table.AddColumn(#"Add Column YEAR_QUARTER_LABEL", "YEAR_MONTH_LABEL", each Number.ToText([YEAR]) & " " & [MONTH_LABEL]),
    #"Add Column SORT_YEAR_QUARTER" = Table.AddColumn(#"Add Column YEAR_MONTH_LABEL", "SORT_YEAR_QUARTER", each Number.ToText([YEAR]) & Text.PadStart(Number.ToText([QUARTER]),2,"0")),
    #"Add Column SORT_YEAR_QUARTER_MONTH" = Table.AddColumn(#"Add Column SORT_YEAR_QUARTER", "SORT_YEAR_QUARTER_MONTH", each [SORT_YEAR_QUARTER] & Text.PadStart(Number.ToText([MONTH]),2,"0")),
    #"Add Column SORT_YEAR_QUARTER_MONTH_DAY" = Table.AddColumn(#"Add Column SORT_YEAR_QUARTER_MONTH", "SORT_YEAR_QUARTER_MONTH_DAY", each [SORT_YEAR_QUARTER_MONTH] & Text.PadStart(Number.ToText([DAY]),2,"0")),
    #"Changed Type" = Table.TransformColumnTypes(#"Add Column SORT_YEAR_QUARTER_MONTH_DAY",{{"DATE", type date}, {"YEAR", Int64.Type}, {"QUARTER", Int64.Type}, {"MONTH", Int64.Type}, {"DAY", Int64.Type}, {"QUARTER_LABEL", type text}, {"MONTH_LABEL", type text}, {"DAY_LABEL", type text}, {"YEAR_QUARTER_LABEL", type text}, {"YEAR_MONTH_LABEL", type text}, {"SORT_YEAR_QUARTER", Int64.Type}, {"SORT_YEAR_QUARTER_MONTH", Int64.Type}, {"SORT_YEAR_QUARTER_MONTH_DAY", Int64.Type}})
in
    #"Changed Type"

The resulting table should appear as follows with one entry for each day in the specified date range. Adjust or add columns as needed to the table. As an example, a new column may be needed to calculate fiscal quarters that may not align to standard calendar quarters, e.g., fiscal Q1 may be October, November, and December instead of January, February, and March.

On the Home ribbon in the Power Query Editor, click Close & Apply to return to Power BI Desktop.

Power BI Desktop – Resulting Date Table
Power BI Desktop – Resulting Date Table

Step 2 – Date Table Adjustments

Returning to the Power BI Desktop, the TBL_PQ_DATE_TABLE is now available in the Fields pane.

Power BI Desktop – Mark as Date Table
Power BI Desktop – Mark as Date Table

If any fields in the TBL_PQ_DATE_TABLE are unexpectedly aggregated, be sure to set the summarization attribute to Don’t summarize. In the Fields pane, click any field in TBL_PQ_DATE_TABLE currently set to summarize. On the Column tools ribbon set Summarization to Don’t Summarize. Repeat until all summarizations are removed.

For each of the text based fields, we need to set the Sort by column attribute. This informs Power BI how to correctly sort label columns in calendar order instead of alphabetically, e.g., January, February, March, etc. Click on each label field and set the Sort by column as follows:

  • QUARTER_LABEL sort by column QUARTER
  • MONTH_LABEL sort by column MONTH
  • DAY_LABEL sort by column DAY_OF_WEEK
  • YEAR_QUARTER_LABEL sort by column SORT_YEAR_QUARTER
  • YEAR_MONTH_LABEL sort by column SORT_YEAR_QUARTER_MONTH
Power BI Desktop – Sort By Column
Power BI Desktop – Sort By Column

Next, we’ll create a year / quarter / month hierarchy. In the Fields pane, click the YEAR field in TBL_PQ_DATE_TABLE. Click the ellipsis next to YEAR and select Create hierarchy. Power BI will add a YEAR Hierarchy under the TBL_PQ_DATE_TABLE. Click the ellipsis next to the QUARTER_LABEL field and select Add to hierarchy and then YEAR hierarchy.

Power BI Desktop – Date Hierarchy
Power BI Desktop – Date Hierarchy

Step 3 – Mark as Date Table

Now we can inform Power BI to use our custom date table. In the Fields pane, click TBL_PQ_DATE_TABLE. In the Table tools ribbon, click Mark as date table followed by Mark as date table. The Mark as date table dialog box opens. Verify that the DATE field is selected in the Date column drop-down list and then click the OK button.

Mark as Date Table – Select Date Column
Mark as Date Table – Select Date Column

Step 4 – Set Data Model Relationships

Switch to the Model view. In this example, I have a simple data table called DATA with a date column and a currency amount column. Create a relationship between the DATE columns in TBL_PQ_DATE_TABLE and the DATA table.

Power BI Desktop – Data Model Relationship Properties
Power BI Desktop – Data Model Relationship Properties

Once the relationship is established, the data model appears as follows.

Power BI Desktop – Data Model
Power BI Desktop – Data Model

Result

Switch to the Report view. To test the new date hierarchy, create a Stacked column chart visualization with the YEAR Hierarchy from the date table TBL_PQ_DATE_TABLE along the Axis and AMOUNT from the DATA table in Values.

Power BI Desktop – Date Table Visualization Fields
Power BI Desktop – Date Table Visualization Fields

The visualization is created with the date hierarchy displayed correctly along the axis after expanding down to the lowest level in the hierarchy.

Power BI Desktop – Date Table Based Visualization
Power BI Desktop – Date Table Based Visualization