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
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
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.
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.
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.
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.
In the Format visual section, turn off Category label. Adjust the font size and any other attributes as needed.
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.
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.
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.
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.
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.
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.
Method 2: Step 6 – Remove Columns
With the exception of the Date modified column, remove all of the other columns from the list.
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.
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.
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.
