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 refresh.zip
Watch the video
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.
Discover how you can automate your work with our Excel courses and tools.
The Excel Academy

Make working late a thing of the past.
The Excel Academy is Excel training for professionals who want to save time.
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?
Filter the Data Modified column to the “Latest” item (it’s in the drop-down list)
Life saver!!! Thank you…
You’re welcome
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.
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!
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.
Great post !
It’s very valuable for medium level powerquery user like me.
I have put your site in my favorite.
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:
https://gorilla.bi/power-query/last-refresh-datetime/
Let me know what you think!
/Rick
Top darts! Great post Mark thank you.