Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Get the refresh date & time in Power Query

Power Query - Date & Time Refresh - Featured Image

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 Icon
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.

New Query - Other Sources - Blank Query

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

= DateTime.LocalNow()

DateTime.LocalNow in Formula Bar

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

Custom Number format to include seconds 

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.

Date Time Refresh



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

FilePath as a single query

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.

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

 Date Time Modified

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.



Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

7 thoughts on “Get the refresh date & time in Power Query

  1. Joep says:

    useful post…what if I wanted to update from folder containing monthly files with the latest file time stamp? i.e. let assume folder has 6 months of data and I add month 7 into folder…how can I get PQ to recognize latest folder time stamp as Date Time Modified?

  2. Oran says:

    Hi , I’m having a similar issue i’d like to solve,
    I’m pulling data to Excell from an SQL Server,
    The SQL server is connected to ERP Oracle Application.

    I’d like to view the pull of information from the ERP to the SQL server Via excell Power Query. All i have is a viewer account to the SQL server so i can pull data.

  3. Alexander Hernandez says:

    Hi,
    How should I write the FilePath in the object query and in the Date Time Modified query if the data is pulled from Sharepoint or Salesforce?

    I’ve been trying to set the path with the salesforce link https://login.salesforce.com/, and it works for setting the FilePath object as the main template does not change at all, but when I set the FilePath object in the Date Time Modified query, I get the following error:
    “Expression.Error: The path is not of a legal form.”

    I would appreciate your support. Cheers!

    • Excel Off The Grid says:

      Sorry, I’m not a SalesForce user, so I can’t add anything to your specific scenario. In SalesForce Are you connecting to a folder? I suspect not, therefore that method might not work with that connector. You might need to use the DataTime.LocalNow() method.

Leave a Reply

Your email address will not be published.