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.
Table of Contents

Download the example file: Join the free Insiders Program and gain access to the example file used for this post.

File name: 0070 Date and time of

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.

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

    //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]



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.


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.


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.

Discover how you can automate your work with our Excel courses and tools.

Excel Academy

Excel Academy
The complete program for saving time by automating Excel.

Excel Automation Secrets

Excel Automation Secrets
Discover the 7-step framework for automating Excel.

Office Scripts Course

Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.

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

  1. 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. 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. 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, 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!

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

  4. Hi Mark,

    That’s a phenomenal solution for Excel files. You mostly use those on your own laptop or in the same company anyway.

    I wanted to share with you my recent discovery in Power BI Service. I encountered an issue with the last refresh time displaying incorrectly, which I later found out was due to the mismatch between the data center location and the timezone.

    I thought your readers might find this information helpful, especially if they are facing similar challenges. I have documented my learnings on this topic, which you can find here:

    Let me know what you think!



Leave a Comment