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

About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match 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:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- 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.
- 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:
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