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

In this example, I will demonstrate two methods to display the last updated date for an Excel file in Power BI. Both methods use an Excel file as the data source, although the second method can work with any file type. To clarify, these methods don’t rely on the Power BI timestamp for when the report was published or last refreshed. Instead, they either retrieve the last save time from Excel or use the Date modified timestamp from the file system.

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 ribbon 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 ribbon in Power BI Desktop, click Get data and select Excel workbook.
2. Select the Excel file you 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 ribbon, 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 your 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 there’s only one entry in the table, but I recommend changing it to Latest just in case.
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 ribbon 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 ribbon, 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 ribbon, 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 ribbon, 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 your 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 there’s only one entry in the table, but I recommend changing it to Latest just in case.
3. In the Format section, turn off the Category label and adjust the font size and other attributes as needed.

Results

Both methods will produce the same result, as shown in the screenshot below. The report displays the last saved date from either the Excel VBA method or the file system’s Date modified timestamp. When the Power BI report is refreshed, the card visualizations will update with the latest date.

Summary

Both methods allow you to display the last updated date of your data in Power BI, either through Excel VBA or Power Query. The Excel VBA method retrieves the last save time from within Excel, while the Power Query method uses the file system’s Date modified timestamp. Choose the method that best suits your data source and reporting needs.