Get the refresh date & time in Power Query

Knowing when our data was last refreshed is essential. It lets us know whether everything is up-to-date. So, in this post, I’ll show you how to get the last refresh date and time using Power Query.

This post is in response to a Power Query question I received from Christian:

“I use a CSV file as a source, and I would like to automatically display its Date Created each time I load a new one using Transform data/Data source settings. …I would like to avoid having to go to the folder a second time and list the files and filter to the right one.”

Christian asked specifically about CSV files, but we should also consider this for live data sources. The approach will be different depending on the source type:

• • Live source (e.g. Database / Website) – We need the date of the last refresh
  • Static source (e.g. Files/Workbooks) – We need the last modified date.

Download the example file

I recommend you download the example file for this post. Then you’ll be able to work along with examples and see the solution in action, plus the file will be helpful for future reference.

Download the file: 0070 Date and time of refresh.zip

Watch the video

Watch the video on YouTube.

Getting last refresh time

Getting the last refresh time is pretty simple.

In Power Query, right-click on the query list pane on the left and select New Query > Other Sources > Blank Query.

Name the query as Date Time Refresh. Then, in the formula bar, enter the following:

= DateTime.LocalNow()

We can now Close and Load this into Excel.

By default, Excel won’t show the seconds. So, to get more detail, change the cell format, using a custom format similar to that below.

dd/mm/yyyy hh:mm:ss

Obviously, if you’re in the US, the format might be:

mm/dd/yyyy hh:mm:ss

Each time we refresh the query, the date and time update accordingly.

Getting the last modification date of a file

The approach above won’t work for static files. The refresh time has little meaning when the source is not constantly updated (CSV, Text, Workbook, etc.). Instead, we need to capture the modified date or created date. I prefer the modified date; if somebody changes a file manually, I want to know the date of those changes.

Getting back to Christian’s original question, unfortunately, it’s not possible to get the modified date directly from a file path. That information is only available as part of the parent folder path. So we’ve just got to get there as efficiently as possible.

We could tackle this in many ways; this is just one possible way, but it will give you the general direction.

Create blank query for the FilePath

Let’s start by creating a blank query; right-click on the query list pane on the left and select New Query > Other Sources > Blank Query.

Enter the file path in the formula bar and call the query FilePath

Return the date modified

For this, we start with a blank query; right-click on the query list pane on the left and select New Query > Other Sources > Blank Query

Then, go into the Advanced Editor by clicking View > Advanced Editor.

We are going to use an Excel-type approach to solve this scenario; by which I mean we will use lots of functions. Enter the following code into the Advanced Editor

let
    //Get the file path
    FilePathString = FilePath,

    //Get length of file path
    FilePathLength = Text.Length(FilePathString),

    //Get last slash position
    LastSlash = Text.PositionOf(FilePathString,"\",Occurrence.Last),

    //Get the folder path
    FolderPath = Text.Start(FilePathString,LastSlash + 1),

    //Get the file name
    FileName = Text.End(FilePathString,FilePathLength - LastSlash - 1),

    //Get the date modified
    DateModified = Folder.Contents(FolderPath){[Name=FileName]}[Date modified]

in
    DateModified

Notes

To aid your understanding of the code above, please read the following notes.

Equivalent Functions

All the functions in the code above are M functions. If you’ve not seen these, I have listed the equivalent Excel functions:

• LEN() = Text.Length()
• LEFT() = Text.Start()
• RIGHT() = Text.End()
• [No simple alternative] = Text.PositionOf()

Folder.Files vs Folder.Contents

When Power Query connects to a folder using the standard user interface, it uses the Folder.Files function, but in this scenario, Folder.Contents is more efficient.

• Folder.Files() – Returns a table containing a row for each file found in the folder path and all its subfolders.
• Folder.Contents() – Returns a table containing a row for each folder and file found in the folder path (but not its subfolders)

We already know the file is in the parent folder, so Folder.Contents is the right option.

Attributes

Within Power Query, we can provide additional attributes to filter and select columns. The code extract below filters the Name field based on the FileName and returns the [Date modified] field.

{[Name=FileName]}[Date modified]

Close and load

Now we can close and load the query into Excel

So now, if the source file is updated, when we click Refresh, we will know when it last changed.

Conclusion

Capturing date and time to show when a source last changed is a good idea. Thankfully, whether it is a “live” or “static” connection, we can do it easily with Power Query.

Hopefully, in the process, you have learned a few things about Power Query functions, and maybe have a little ess fear about the Advanced Editor.