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