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

Custom Power BI Date Tables Using Power Query

By default, Power BI creates a date hierarchy based on the date data it identifies in the data model. This default hierarchy may be sufficient in many instances. However, you may quickly find it produces unexpected results in some visualizations. Custom date tables in Power BI provide fine-tuned control over date hierarchies. By creating a custom date table, you have complete control over the date hierarchy behavior as well as the calculation and formatting of various date dimensions.

Step 1 – Create Date Table Using Power Query

In this example, Power Query is used to generate the date table. I chose Power Query out of personal preference, but the date table may also be created using a DAX expression in Power BI.

On the Home ribbon, click Get data and select Blank query. The Power Query Editor opens to a blank query. On the Home ribbon in the Power Query Editor, click Advanced Editor and add the following code. Adjust the StartDate and EndDate values to satisfy the minimum and maximum date range needed in your specific report. Click Done to close the Advanced Editor. After returning to the Power Query Editor, rename the table in the Query Settings pane under the PROPERTIES section. I use the name TBL_PQ_DATE_TABLE.

let
    StartDate = #date(2022, 1, 1),
    EndDate = #date(2022, 12, 31),
    DateList = List.Dates(StartDate, Number.From(EndDate) - Number.From(StartDate) + 1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "DATE"}}),
    #"Add Column YEAR" = Table.AddColumn(#"Renamed Columns", "YEAR", each Date.Year([DATE])),
    #"Add Column QUARTER" = Table.AddColumn(#"Add Column YEAR", "QUARTER", each Date.QuarterOfYear([DATE])),
    #"Add Column MONTH" = Table.AddColumn(#"Add Column QUARTER", "MONTH", each Date.Month([DATE])),
    #"Add Column DAY" = Table.AddColumn(#"Add Column MONTH", "DAY", each Date.Day([DATE])),
    #"Add Column QUARTER_LABEL" = Table.AddColumn(#"Add Column DAY", "QUARTER_LABEL", each "Q" & Number.ToText([QUARTER])),
    #"Add Column MONTH_LABEL" = Table.AddColumn(#"Add Column QUARTER_LABEL", "MONTH_LABEL", each Date.ToText([DATE],"MMM")),
    #"Add Column DAY_LABEL" = Table.AddColumn(#"Add Column MONTH_LABEL", "DAY_LABEL", each Date.ToText([DATE],"ddd")),
    #"Add Column DAY_OF_WEEK" = Table.AddColumn(#"Add Column DAY_LABEL", "DAY_OF_WEEK", each Date.DayOfWeek([DATE])),
    #"Add Column YEAR_QUARTER_LABEL" = Table.AddColumn(#"Add Column DAY_OF_WEEK", "YEAR_QUARTER_LABEL", each Number.ToText([YEAR]) & " " & [QUARTER_LABEL]),
    #"Add Column YEAR_MONTH_LABEL" = Table.AddColumn(#"Add Column YEAR_QUARTER_LABEL", "YEAR_MONTH_LABEL", each Number.ToText([YEAR]) & " " & [MONTH_LABEL]),
    #"Add Column SORT_YEAR_QUARTER" = Table.AddColumn(#"Add Column YEAR_MONTH_LABEL", "SORT_YEAR_QUARTER", each Number.ToText([YEAR]) & Text.PadStart(Number.ToText([QUARTER]),2,"0")),
    #"Add Column SORT_YEAR_QUARTER_MONTH" = Table.AddColumn(#"Add Column SORT_YEAR_QUARTER", "SORT_YEAR_QUARTER_MONTH", each [SORT_YEAR_QUARTER] & Text.PadStart(Number.ToText([MONTH]),2,"0")),
    #"Add Column SORT_YEAR_QUARTER_MONTH_DAY" = Table.AddColumn(#"Add Column SORT_YEAR_QUARTER_MONTH", "SORT_YEAR_QUARTER_MONTH_DAY", each [SORT_YEAR_QUARTER_MONTH] & Text.PadStart(Number.ToText([DAY]),2,"0")),
    #"Changed Type" = Table.TransformColumnTypes(#"Add Column SORT_YEAR_QUARTER_MONTH_DAY",{{"DATE", type date}, {"YEAR", Int64.Type}, {"QUARTER", Int64.Type}, {"MONTH", Int64.Type}, {"DAY", Int64.Type}, {"QUARTER_LABEL", type text}, {"MONTH_LABEL", type text}, {"DAY_LABEL", type text}, {"YEAR_QUARTER_LABEL", type text}, {"YEAR_MONTH_LABEL", type text}, {"SORT_YEAR_QUARTER", Int64.Type}, {"SORT_YEAR_QUARTER_MONTH", Int64.Type}, {"SORT_YEAR_QUARTER_MONTH_DAY", Int64.Type}})
in
    #"Changed Type"

The resulting table should appear as follows with one entry for each day in the specified date range. Adjust or add columns as needed to the table. As an example, a new column may be needed to calculate fiscal quarters that may not align to standard calendar quarters, e.g., fiscal Q1 may be October, November, and December instead of January, February, and March.

On the Home ribbon in the Power Query Editor, click Close & Apply to return to Power BI Desktop.

Power BI Desktop – Resulting Date Table
Power BI Desktop – Resulting Date Table

Step 2 – Date Table Adjustments

Returning to the Power BI Desktop, the TBL_PQ_DATE_TABLE is now available in the Fields pane.

Power BI Desktop – Mark as Date Table
Power BI Desktop – Mark as Date Table

If any fields in the TBL_PQ_DATE_TABLE are unexpectedly aggregated, be sure to set the summarization attribute to Don’t summarize. In the Fields pane, click any field in TBL_PQ_DATE_TABLE currently set to summarize. On the Column tools ribbon set Summarization to Don’t Summarize. Repeat until all summarizations are removed.

For each of the text based fields, we need to set the Sort by column attribute. This informs Power BI how to correctly sort label columns in calendar order instead of alphabetically, e.g., January, February, March, etc. Click on each label field and set the Sort by column as follows:

  • QUARTER_LABEL sort by column QUARTER
  • MONTH_LABEL sort by column MONTH
  • DAY_LABEL sort by column DAY_OF_WEEK
  • YEAR_QUARTER_LABEL sort by column SORT_YEAR_QUARTER
  • YEAR_MONTH_LABEL sort by column SORT_YEAR_QUARTER_MONTH
Power BI Desktop – Sort By Column
Power BI Desktop – Sort By Column

Next, we’ll create a year / quarter / month hierarchy. In the Fields pane, click the YEAR field in TBL_PQ_DATE_TABLE. Click the ellipsis next to YEAR and select Create hierarchy. Power BI will add a YEAR Hierarchy under the TBL_PQ_DATE_TABLE. Click the ellipsis next to the QUARTER_LABEL field and select Add to hierarchy and then YEAR hierarchy.

Power BI Desktop – Date Hierarchy
Power BI Desktop – Date Hierarchy

Step 3 – Mark as Date Table

Now we can inform Power BI to use our custom date table. In the Fields pane, click TBL_PQ_DATE_TABLE. In the Table tools ribbon, click Mark as date table followed by Mark as date table. The Mark as date table dialog box opens. Verify that the DATE field is selected in the Date column drop-down list and then click the OK button.

Mark as Date Table – Select Date Column
Mark as Date Table – Select Date Column

Step 4 – Set Data Model Relationships

Switch to the Model view. In this example, I have a simple data table called DATA with a date column and a currency amount column. Create a relationship between the DATE columns in TBL_PQ_DATE_TABLE and the DATA table.

Power BI Desktop – Data Model Relationship Properties
Power BI Desktop – Data Model Relationship Properties

Once the relationship is established, the data model appears as follows.

Power BI Desktop – Data Model
Power BI Desktop – Data Model

Result

Switch to the Report view. To test the new date hierarchy, create a Stacked column chart visualization with the YEAR Hierarchy from the date table TBL_PQ_DATE_TABLE along the Axis and AMOUNT from the DATA table in Values.

Power BI Desktop – Date Table Visualization Fields
Power BI Desktop – Date Table Visualization Fields

The visualization is created with the date hierarchy displayed correctly along the axis after expanding down to the lowest level in the hierarchy.

Power BI Desktop – Date Table Based Visualization
Power BI Desktop – Date Table Based Visualization

Display Total Inside Power BI Donut Chart

Donut charts – second only to Pie charts as the tastiest of all visualizations – are used to illustrate proportions of individual components to the overall whole. They are very similar to Pie charts except the center is hollow although that space usually contains additional information. It has been my experience that Donut charts tend to be favored over Pie charts because they provide increased data density (more information in a single visualization) and they look more modern as compared with old Pie Charts.

While Power BI does contain a Donut visualization, it does not have any out-of-the-box options to fill the center. However, we can create a filled Donut chart using a combination of Data Analysis Expressions (DAX) measures and Card visualizations.

In this example, the Donut chart illustrates spend in various categories. In a default Donut chart, the magnitude and proportion of the individual components are apparent, but the overall total is unknown.

Donut Chart Example
Donut Chart Example

The Data

The data used in this example is found in the screenshot below. It’s a simple Excel sheet with various categories in the rows and months along the columns. For the Donut chart, we want to display the “Category” totals associated with actual spend only, e.g., “Actual – Payroll”, “Actual – Facilities”, “Actual – Telecom”, and “Actual – External”. In other words, we only want the sum of “Total” (Column N) associated with rows 4 through 7.

The values are formatted using a custom number format (referenced below) to display in millions. For instance, if the underlying cell value is 1,000,000, then Excel displays the value as 1.0 using this format.

#,##0.0,,;[Red](#,##0.0,,)
Excel – Donut Sample Data
Excel – Donut Sample Data

Step 1 – Import Data

In the example Excel file, the data is located on a worksheet named “DATA”. Starting with a new Power BI report, click Import data from Excel and select the appropriate file. Perform any initial cleanup transformations as needed. The import results in a Power BI table named “Table 1 (DATA)” which I have renamed to the much more informative name “DATA”.

Power BI Desktop – Imported Sample Data
Power BI Desktop – Imported Sample Data

Step 2 – Add Helper Column

As I mentioned earlier, we only want the Donut chart to display entries associated with actual spend so we add a column to help constrain and filter the data in later steps. In the example data, the rows containing actual spend are conveniently prefixed with the string “ACTUAL – ” which makes the logic straightforward. In this snippet, the new column contains the text “ACTUAL” if the “Category” column begins with the text “ACTUAL – ” in the leftmost substring. All other rows are tagged as “OTHER”. For readability, I use the LEN() function instead of counting the characters in the prefix.

COLUMN_ACTUAL_CATEGORY = IF(LEFT('DATA'[Category],LEN("ACTUAL - ")) = "ACTUAL - ", "ACTUAL", "OTHER")

Our data table now appears (below) with the helper column added to the structure. The appropriate rows are tagged with the text “ACTUAL” in the new column.

Power BI Desktop – Adjusted Sample Data
Power BI Desktop – Adjusted Sample Data

Step 3 – Create DAX Measures

Now that we have our data structured and tagged, we need to create a few DAX measures to populate the center of the Donut chart.

The first measure retrieves the total of all the actual spend entries. Since the sample data already includes a total row, this is trivial since we can simply look for the entry where “Category” is equal to “Total”. Since the underlying table may be influenced by other filters, we use the ALL function in the FILTER for the CALCULATE function in order to ignore any other applied filters and return all rows in the table.

MEASURE_TOTAL_ACTUAL_SPEND_USING_CALCULATE = CALCULATE(SUM('DATA'[Total]),FILTER(ALL('DATA'),'DATA'[Category] = "TOTAL"))

The second measure calculates the sum of the “Total” column in the DATA table. In this measure, the ALL function is not used since we only want to include entries where the helper column is tagged as “ACTUAL” and we want the calculated to be influenced by user selections and filters in the Donut chart visualization, e.g., if the user selects a category in the visualization, then the center calculation should adjust.

MEASURE_SUM_FILTERED_ACTUAL_SPEND_USING_SUM = CALCULATE(SUM('DATA'[Total]),FILTER('DATA','DATA'[COLUMN_ACTUAL_CATEGORY] = "ACTUAL"))

The third and final measure calculates the percentage of the selected entries against the whole. By default, the percentage is 100% since all entries are initially selected.

MEASURE_PERCENT_OF_TOTAL = DIVIDE([MEASURE_SUM_FILTERED_ACTUAL_SPEND_USING_SUM],[MEASURE_TOTAL_ACTUAL_SPEND_USING_CALCULATE],0)

Click on the newly created MEASURE_PERCENT_OF_TOTAL in the list of Fields. Click on the Measure tools ribbon and change the Format to Percentage. Also change the number of decimal places shown for this value to 1.

Power BI Desktop – Change Measure Format to Percentage
Power BI Desktop – Change Measure Format to Percentage

Step 3 – Create Donut Chart

Switch to the Report view and add a Donut chart visualization. Using the sample data, the Details use the “Category” field and the Values use the “Total” field. The Donut chart displays all of the entries in the data table so we’ll need to use the helper column added earlier. In Filters, use the Filters on this page to include the “COLUMN_ACTUAL_CATEGORY” helper column and select only entries tagged as “ACTUAL”.

Power BI Desktop – Donut Chart Configuration
Power BI Desktop – Donut Chart Configuration

In the Format settings, turn off Background and in the Detail labels section change the Value decimal places to 1.

It is important to turn off the background color so that the center of the Donut chart is transparent. This allows the additional information to be visible in the center.

Step 4 – Create Card Visualizations

We will need two Card visualizations to fill the center of the Donut chart.

Insert a new Card visualization. The first Card represents the second DAX measure created in Step 3 called “MEASURE_SUM_FILTERED_ACTUAL_SPEND_USING_SUM”. This is the total of all selected and filtered elements of the Donut chart. Include “MEASURE_SUM_FILTERED_ACTUAL_SPEND_USING_SUM” in the Fields for this Card.

In the Format settings, turn off Category and in the Data label section change the Value decimal places to 1 and turn on Bold.

Power BI Desktop – Card Visualization Total
Power BI Desktop – Card Visualization Total

Insert a new Card visualization. The second Card represents the third DAX measure created in Step 3 called “MEASURE_PERCENT_OF_TOTAL”. This is the percentage of the selected and filtered entries in the Donut chart against the total of all actual spend entries. Include “MEASURE_PERCENT_OF_TOTAL” in the Fields for this Card.

In the Format settings, turn off Category and in the Data label section change the Value decimal places to 1.

Power BI Desktop – Card Visualization Percentage
Power BI Desktop – Card Visualization Percentage

Step 5 – Formatting and Clean-up

Now that all of the individual pieces have been created, it is time to put them together into the final product. Align the two Cards in the center of the Donut chart. Adjust the sizing of the Donut chart as well as the Cards and their associated fonts until everything looks as expected. Bring the Donut chart to the front or send the two Cards to the back so that the corners of the Cards do not visually interfere with the Donut chart.

Result

The final chart shows the calculations in the center of the Donut chart as expected.

Power BI Desktop – Donut Chart With Total Inside
Power BI Desktop – Donut Chart With Total Inside

If the user selects or filters on one or more segments of the Donut chart, then the center Cards and underlying DAX measures are recalculated to reflect those selections (screenshot below).

Power BI Desktop – Donut Chart With Filtered Total Inside
Power BI Desktop – Donut Chart With Filtered Total Inside