How to Display the File Last Updated Date in Power BI Using Excel VBA or Power Query

In this article, two methods to display the last updated date for an Excel file in Power BI are demonstrated. Both methods use an Excel file as the data source, although the second method can work with any file type. To clarify, these methods do not rely on the Power BI timestamp for when the report was published or last refreshed. Instead, the first method retrieves Excel’s internal Last Save Time property, while the second retrieves the file system’s Date modified timestamp.

Instructions

Method 1: Excel VBA

The first method uses a custom VBA function in Excel to retrieve the Last Save Time property.

Step 1: Create a User-Defined Function (UDF) to Retrieve Last Save Time

1. From the Developer tab in Excel, click Visual Basic.
2. The Microsoft Visual Basic for Applications window will open.
3. From the Insert menu, click Module to add a new module (named Module1 in this example).
4. Open Module1 and add the following code to create the custom function that retrieves the last save timestamp:

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

Step 2: Force Full Recalculation Before Save

1. From Microsoft Visual Basic for Applications, open ThisWorkbook.
2. Add the following code. This code forces a full recalculation of the workbook and updates the timestamp before saving the file.
3. 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

Step 3: Retrieve Last Save Time

1. From Excel, create a new worksheet (named EXAMPLE in this case).
2. In cell A1, enter LAST SAVED DATE.
3. In cell A2, use the custom VBA function by entering:

=Last_Saved_Timestamp()

4. The timestamp will appear as a serial number.
5. Save and close the Excel file.

Step 4: Load Data into Power BI

1. From the Home tab in Power BI Desktop, click Get data and select Excel workbook.
2. Select the Excel file created earlier and click the Open button.
3. In the Navigator window, check EXAMPLE and click the Load button.
4. The EXAMPLE table and the LAST SAVED DATE field will be available in the Data pane.
5. If LAST SAVED DATE is summarized, turn off summarization. In the Data pane, click LAST SAVED DATE, then from the Column tools tab, select Don’t summarize from the Summarization drop-down.
6. In the Data type drop-down, select Date. If prompted with a Data type change warning, click the Yes button.
7. In the Format drop-down, choose the desired date format.

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

1. Switch to Report view, add a Card visualization, and drag LAST SAVED DATE from EXAMPLE into the Fields section.
2. If it defaults to Earliest, change it to Latest. This step may not be necessary if only one entry exists in the table, but changing it to Latest is recommended as a precaution.
3. In the Format section, turn off the Category label and adjust the font size and other attributes as needed.

Method 2: Power Query File Properties

The second method uses Power Query to retrieve the Date modified timestamp from the file system properties. This method works with any file type, not just Excel.

Step 1: Get Data

1. From the Home tab in Power BI Desktop, click Get data and select More....
2. In the Get Data dialog box, choose File and then Folder, and click the Connect button.
3. In the Folder dialog box, enter or browse to the parent folder of the data file. Click the OK button.
4. Another dialog box will appear, listing the files in the selected folder. Click the Transform Data button.
5. The Power Query window will open. The first step in the query is labeled Source, which returns the file list. Verify that the list is correct.
6. Filter the Name column so that only the file used by the Power BI report appears.
7. Select the Date modified column. From the Home tab, select Remove Columns and Remove Other Columns.
8. Rename the Date modified column to LAST SAVED DATE to maintain consistency with the first method.
9. In the Query Settings pane under Properties, rename the query to something descriptive, like FOLDER EXAMPLE. This name will appear in the Data pane in Power BI.
10. From the Home tab, click Close & Apply.

Here is the final query code after applying all filters and transformations:

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

NOTE: In this demonstration, the file is located at C:\test\example.xlsm. Adjust the query as necessary.

Step 2: Load Data into Power BI

1. Returning to Power BI, the FOLDER EXAMPLE table and the LAST SAVED DATE field will be available in the Data pane.
2. If LAST SAVED DATE is summarized, turn off summarization. In the Data pane, click LAST SAVED DATE, then from the Column tools tab, select Don’t summarize from the Summarization drop-down.
3. In the Data type drop-down, select Date. If prompted with a Data type change warning, click the Yes button.
4. In the Format drop-down, choose the desired date format.

Step 3: Display Data Last Updated Date Using a Card Visualization

1. Switch to Report view, add another Card visualization, and drag LAST SAVED DATE from FOLDER EXAMPLE into the Fields section.
2. If it defaults to Earliest, change it to Latest. This step may not be necessary if only one entry exists in the table, but changing it to Latest is recommended as a precaution.
3. In the Format section, turn off the Category label and adjust the font size and other attributes as needed.

Results

Both methods produce the expected timestamp in this example, as shown in the screenshot below. When the Power BI report is refreshed, each card visualization updates to reflect the latest value returned by its respective method.

Summary

Both methods display a timestamp associated with the source file in Power BI. The Excel VBA method retrieves Excel’s internal Last Save Time property, while the Power Query method uses the file system’s Date modified timestamp. Although these values often match, they originate from different sources and may differ depending on how the file is modified. The best method depends on the data source and reporting requirements.