A frequent question I have received is “How do I add the last refresh date and time to Power BI reporting?”. Great news, this solution is very simple! The code I provide in this article will allow you to create a source, and then use this in one or more of your visuals so that it’s always clear to you and your end users when the report was last refreshed.
Note: if you need refresh times for your individual sources, one solution would be to add a column with DateTime.LocalNow() and then work a DAX measure off of that once the data from that source is loaded for the first time. Definitely comment below if you would like me to expand on that.
Adding the Refresh Source
You are simply going to add a new source, choose Blank from the menu.
Click Advanced Editor to open up the M code window, then copy in the code below.
Power Query Code (M) – Power BI Last Refresh Date and Time
|
1 2 3 4 5 6 7 8 |
let Source = #table(type table[Date Last Refreshed=datetime], {{DateTime.LocalNow()}}), #"Added Custom" = Table.AddColumn(Source, "Updated", each "Updated "), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each [Updated]&Text.From([Date Last Refreshed])), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Date Last Refreshed", "Updated"}), #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "LastUpdate"}}) in #"Renamed Columns1" |
Click Done. You will notice that I have added the word “Updated” ahead of the date and time in this code. I prefer to display the measure this way in my reporting. If you would prefer to just have the date and time, simply delete the steps after Source in the Query Settings at the right. I find it’s best to work from the bottom, up when removing steps.
Of course, you can also edit the output as you see fit, displaying in the way that works best for your report.
Adding the Last Refresh Date and Time to the Report
After Applying the change, you should now see the source and column listed.
I find it easiest to add a card visual to display the date and time. Simply select the card visual and then select the LastUpdate column. You can then edit the visual as you see fit for your report.
Final Output
As always, I hope this post has helped you out. Please subscribe to be notified of the latest posts and leave a comment if you have any topics you’d like to see covered here. Thanks!
Topics covered in this post: Power Query Last Refresh Date and Time, Power Query M Current Date and Time, Last Refresh Date and Time in Power BI