Handling Paginated REST API Results with Power Query

Querying a RESTful API, ingesting its response, and transforming the data through Power Query is a powerful capability. In many instances, the integration between Power Query and Power BI or Power Query and Excel further simplifies the process by eliminating the need for other tools and scripts to perform intermediate processing of a JSON response. However, working with REST APIs is not always straightforward as there can be complexities with larger data sets. To limit the processing and bandwidth burden on an API, a response may be paginated to limit the number of results returned for any given query. In other words, if a particular query is expected to return a million entries, then the API may paginate those million entries into blocks of 100 entries per page. To retrieve the full million entry result set would require ten thousand separate API requests to retrieve each 100-entry page (100 * 10,000 = 1,000,000).

The purpose of this post is to demonstrate Power Query code that can handle paginated JSON results from a REST API. Three solutions are presented to handle API request scenarios where the number of pages is known, calculated, or unknown.

REST API

The code presented in this solution uses a publicly available REST API from the U.S. Department of the Treasury named the Treasury Reporting Rates of Exchange. The intent of this API is to provide the U.S. government’s authoritative quarterly exchange rates for converting foreign currency units and U.S. dollar equivalents. I chose this particular API because the basic concept of an exchange rate is commonly understood albeit entirely unimportant for the purposes of this exercise. More importantly, this API exposes a significant volume data needed to demonstrate pagination. I recommend exploring the full U.S. Treasury Fiscal Data site as there are quite a few data sources which are well-documented, free-to-use without requiring a sign-up or API token, and they provide a great opportunity to learn how to interact with APIs as well as develop analytics using real-world data.

The API query used in this post is straightforward. The three data fields requested from the Rates of Exchange API are Country – Currency Description, Exchange Rate, and Record Date. As illustrated in the screenshot below, the JSON response contains some meta data including count, total-count, and total-pages. For this demonstration, these particular fields and their corresponding values are important to note for final verification that the Power Query code is working as expected. The count field corresponds to the number of entries returned, the total-count field corresponds to the total number of entries in the result set, and the total-pages field corresponds to the total number of paginated results, e.g., 100 maximum results across 162 total pages (page index 162 is a partial page with only 10 entries).

API JSON Results
API JSON Results

Custom Power Query Paging Function

According to the documentation for this API, there are two defined parameters with respect to response pagination. The page[size]= parameter sets the number of entries returned in the response. The default response for page[size]= is 100 entries per page. This example uses the default setting. The more important parameter for this demonstration is the page[number]= parameter as it will be used to iterate through each page number. The page[number]= parameter sets the pagination index which is used to return a particular page from the resulting data set. As an example, if the resulting data set has 42 total pages and the page[number]= parameter is set to 7, then the response will return the block of 100 entries associated with page 7.

Function Source Code

We will need to create a custom Power Query function to process a dynamic number of pages returned by an API. This function defines one parameter named page which represents the page number to retrieve and it is used in the API query request. This function will be used in each of the three solutions.

  1. From Power BI or Excel (or any other client application that supports Power Query), select Get data and then Blank query.
  2. The Power Query window opens to a new blank query.
  3. From the Power Query Home menu, select Advanced Editor.
  4. Paste the following code into the Advanced Editor replacing any pre-existing default code.
  5. Click Done to close the Advanced Editor window.
  6. In the Query Settings pane under Properties, rename the function to GetPage.
(page as text) =>
let
    Source = try Json.Document(Web.Contents("https://api.fiscaldata.treasury.gov/services/api/fiscal_service/v1/accounting/od/rates_of_exchange?page[number]=" & page & "&fields=country_currency_desc,exchange_rate,record_date")) otherwise [data=null, meta=null, links=null],
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Removed Columns" = Table.RemoveColumns(#"Converted to Table",{"meta", "links"}),
    #"Expanded data List" = Table.ExpandListColumn(#"Removed Columns", "data"),
    #"Expanded data Records" = Table.ExpandRecordColumn(#"Expanded data List", "data", {"country_currency_desc", "exchange_rate", "record_date"}, {"data.country_currency_desc", "data.exchange_rate", "data.record_date"})
in
    #"Expanded data Records"
Power Query – Advanced Editor GetPage Function
Power Query – Advanced Editor GetPage Function

Function Test

  1. To test the function, click GetPage on the Queries pane.
  2. The Enter Parameter screen is displayed with a text field corresponding to the page parameter.
Power Query – GetPage Function
Power Query – GetPage Function
  1. Enter a valid page number index. Invalid values will return a null table.
  2. Click the Invoke button.
Power Query – GetPage Function Test Value
Power Query – GetPage Function Test Value
  1. The Invoked Function returns a table containing the Country – Currency Description, Exchange Rate, and Record Date entries from the supplied page number index as expected.
Power Query – Invoked GetPage Function Test
Power Query – Invoked GetPage Function Test

At this point we have a function that is able to retrieve a single indexed page within the result set. We can now use this function to iterate through each indexed page to dynamically retrieve and aggregate all of the paginated results.

Retrieving All Pages

This particular API conveniently returns the total number of pages as part of its JSON response which makes page retrieval fairly easy. Many other APIs are not as helpful. Some APIs may only return the total number of entries in the response. Other APIs may not return any counts. For those less accommodating APIs, I have provided alternate solutions to either calculate pages or retrieve pages dynamically without meta counts.

  1. From the Power Query Home ribbon, select New Source and then Blank Query.
  2. The Power Query window opens a new blank query.
  3. From the Power Query Home menu, select Advanced Editor.
  4. Paste one of the following code sequences into the Advanced Editor replacing any pre-existing default code.
  5. Click Done to close the Advanced Editor window.
  6. In the Query Settings pane under Properties, rename the function to Treasury Reporting Rates of Exchange.

Method 1: Number of Pages is Known Upfront

In the easiest scenario, the API response includes a count of the total number of pages. No additional calculation is needed.

let
    Source = try Json.Document(Web.Contents("https://api.fiscaldata.treasury.gov/services/api/fiscal_service/v1/accounting/od/rates_of_exchange?fields=country_currency_desc,exchange_rate,record_date")) otherwise [data=null, meta=null, links=null],
    #"Page List" = if Source = null or Source[meta] = null then {null} else {1..Source[meta][#"total-pages"]},
    #"Converted to Table" = Table.FromList(#"Page List", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "PAGES"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"PAGES", type text}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "GetPage", each if [PAGES] <> null then GetPage([PAGES]) else null),
    #"Expanded GetPage" = Table.ExpandTableColumn(#"Invoked Custom Function", "GetPage", {"data.country_currency_desc", "data.exchange_rate", "data.record_date"}, {"GetPage.data.country_currency_desc", "GetPage.data.exchange_rate", "GetPage.data.record_date"})
in
    #"Expanded GetPage"
Power Query – Retrieve Full Result Set When Number of Pages Is Known
Power Query – Retrieve Full Result Set When Number of Pages Is Known

Method 2: Number of Pages is Unknown; Number of Entries is Known

If the number of pages is unknown because the API doesn’t expose it in the meta data, then the page indexes can be calculated using other available values. If the API returns the total number of entries in the result set and the number of entries per response is known, then the number of pages in the paginated results can be calculated using simple division and rounding up. The bulk of this code is the same as above. The relevant change is found in step #”Page List”. In this step the dynamic list generation is changed to {1..Number.RoundUp(Source[meta][#"total-count"] / Source[meta][#"count"])} which calculates the number of pages by dividing the total-count (number of entries in the result set) by count (number of entries per page). The Number.RoundUp function is then used to account for partial pages by rounding the remainder up to the next integer. In other words, if the total number of entries in the result set is not evenly divided by the total entries per page, then the remainder will be on a partial page. As an example, if there are 205 total entries with 100 entries per page, then the number of pages should be 3 to account for the 5 entries above 200. This calculation would need to be adjusted if the API pagination started at zero or some other number.

let
    Source = try Json.Document(Web.Contents("https://api.fiscaldata.treasury.gov/services/api/fiscal_service/v1/accounting/od/rates_of_exchange?fields=country_currency_desc,exchange_rate,record_date")) otherwise [data=null, meta=null, links=null],
    #"Page List" = if Source = null or Source[meta] = null then {null} else {1..Number.RoundUp(Source[meta][#"total-count"] / Source[meta][#"count"])},
    #"Converted to Table" = Table.FromList(#"Page List", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "PAGES"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"PAGES", type text}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "GetPage", each if [PAGES] <> null then GetPage([PAGES]) else null),
    #"Expanded GetPage" = Table.ExpandTableColumn(#"Invoked Custom Function", "GetPage", {"data.country_currency_desc", "data.exchange_rate", "data.record_date"}, {"GetPage.data.country_currency_desc", "GetPage.data.exchange_rate", "GetPage.data.record_date"})
in
    #"Expanded GetPage"
Power Query – Retrieve Full Result Set When Number of Pages Is Unknown
Power Query – Retrieve Full Result Set When Number of Pages Is Unknown

Method 3: Both Number of Pages and Number of Entries are Unknown

In this scenario, which is the most complex of the three, both the total number of pages nor the total number of entries are unknown. We need to use entirely different logic since the API does not expose any meta data with respect to result counts. The function List.Generate is used to iterate through pages until a null page is retrieved, i.e., the page contains an entry with null values. This condition depends on the custom GetPage function returning null when a indexed page number does not return any results. The conditional should be adjusted to the particular API.

let
    Source = List.Generate(() => [PAGE = 1, RESULTS = GetPage("1")], each Table.RowCount(Table.SelectRows(_[RESULTS], each not List.Contains(Record.ToList(_), null))) > 0, each [PAGE = _[PAGE] + 1, RESULTS = GetPage(Number.ToText([PAGE] + 1))], each _[[PAGE], [RESULTS]]),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"PAGE", "RESULTS"}, {"PAGES", "GetPage"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column",{{"PAGES", type text}}),
    #"Expanded GetPage" = Table.ExpandTableColumn(#"Changed Type", "GetPage", {"data.country_currency_desc", "data.exchange_rate", "data.record_date"}, {"GetPage.data.country_currency_desc", "GetPage.data.exchange_rate", "GetPage.data.record_date"})
in
    #"Expanded GetPage"
Power Query – Retrieve Full Result Set When Both Number of Pages and Number of Entries are Unknown
Power Query – Retrieve Full Result Set When Both Number of Pages and Number of Entries are Unknown

Code Explanation

Method 1 and Method 2

Step 1 – Source

The first step requests the API as described above. The purpose of this step is to retrieve the count and total-count from the meta record. Inspecting those values confirms the expected result found in the earlier screenshot of the direct API request. It’s worth noting that the API request is wrapped in a try...otherwise statement. If the API request fails, then null values are returned by default for the data, meta, and links records. This helps prevent the overall Power Query query and any downstream Power Query queries from failing if the API request is unsuccessful for any number of reasons, e.g., network issues, server issues, etc.

Power Query – Methods 1 & 2 – Query Step 1 – Source
Power Query – Methods 1 & 2 – Query Step 1 – Source

Step 2 – Page List

Using the value of total-count, the next step generates a list of page indexes starting from 1 and ending with the total number of pages. This particular API always begins with a page index numbered 1, but other APIs may start with a different value. This step also includes a basic error check for null values returned from a failed API request.

Power Query – Methods 1 & 2 – Query Step 2 – Page List
Power Query – Methods 1 & 2 – Query Step 2 – Page List

Step 3 – Converted to Table

The dynamic list generated in the previous step is converted to a table containing a single column of page indexes.

Power Query – Methods 1 & 2 – Query Step 3 – Converted to Table
Power Query – Methods 1 & 2 – Query Step 3 – Converted to Table

Step 4 – Renamed Columns

The single column in the table of page indexes is renamed to PAGES.

Power Query – Methods 1 & 2 – Query Step 4 – Renamed Columns
Power Query – Methods 1 & 2 – Query Step 4 – Renamed Columns

Step 5 – Changed Type

The PAGES column is changed to a text data type.

Power Query – Methods 1 & 2 – Query Step 5 – Changed Type
Power Query – Methods 1 & 2 – Query Step 5 – Changed Type

Step 6 – Invoked Custom Function

With a list of valid page indexes, we can now call the custom GetPage function to retrieve the entries associated with a particular page from the API. From the Add Column ribbon, select Invoke Custom Function to call GetPage using the values found in the PAGES column as the argument. For each value in the PAGES column, the GetPage function is invoked and the API is requested. The new GetPage column contains a table of entries from each indexed page API request.

Power Query – Methods 1 & 2 – Query Step 6 – Invoked Custom Function
Power Query – Methods 1 & 2 – Query Step 6 – Invoked Custom Function

Step 7 – Expanded GetPage

Expanding the tables found in the GetPage column created in the prior step displays all of the expected entries.

Power Query – Methods 1 & 2 – Query Step 7 – Expanded GetPage
Power Query – Methods 1 & 2 – Query Step 7 – Expanded GetPage

Results

After returning the results from Power Query to Power BI, a simple table visualization is used to confirm that the number of pages and the number of entries match the total counts from the original API request and the code is working as expected.

Power BI – Methods 1 & 2 – Result Counts
Power BI – Methods 1 & 2 – Result Counts

Method 3

Step 1 – Source

Unlike the other methods, the total number of pages and the total number of entries are unknown. This code statement assumes the API page indexes begin at number 1. The List.Generate function uses a conditional to check for any null values in returned records. Given the way the GetPage function is coded, there should not be any null values unless the page returns an empty result set.

Power Query – Method 3 – Query Step 1 – Source
Power Query – Method 3 – Query Step 1 – Source

Step 2 – Converted to Table

The interim list of records is converted to a table of records in one column named Column1.

Power Query – Method 3 – Query Step 2 – Converted to Table
Power Query – Method 3 – Query Step 2 – Converted to Table

Step 3 – Expanded Column

The records are expanded into a column for page indexes named PAGES and a column containing tables of results per page named GetPage.

Power Query – Method 3 – Query Step 3 – Expanded Column
Power Query – Method 3 – Query Step 3 – Expanded Column

Step 4 – Changed Type

The PAGES column is changed to the text data type.

Power Query – Method 3 – Query Step 4 – Changed Type
Power Query – Method 3 – Query Step 4 – Changed Type

Step 5 – Expanded GetPage

Expanding the tables found in the GetPage column created in the prior step displays all of the expected entries.

Power Query – Method 3 – Query Step 5 – Expanded GetPage
Power Query – Method 3 – Query Step 5 – Expanded GetPage

Results

After returning the results from Power Query to Power BI, a simple table visualization is used to confirm that the number of pages and the number of entries match the total counts from the original API request and the code is working as expected.

Power BI – Method 3 – Result Counts
Power BI – Method 3 – Result Counts

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