Sorry for the dumb question - do I need a relationship between this table and my main Date table in order for this to work.
Guy
1 Like
Hi Guy,
No dumb questions here, so thanks for asking! The answer is - no you don’t.
This is just a supporting table who’s sole task is to facilitate you in making the Last Refresh date visible in your report.
You can even hide the table in Report View.
2 Likes
@Melissa Just added this code to a few of my Reports and the consumers of the data in the Service loved it.
These are the same people who was always asking "when was the last time this data was updated "
One satisfied customer here.
Guy
1 Like
Excellent! And now they have the answer… sometimes it’s a little thing that can make a big difference.
Thanks for sharing your feedback @GuyJohnson
Melissa,
Only issue with this is when this shows up in Power BI service, it is always stored in UTC Time. Just saw a video for a little work around this. Let me know if you have any other suggestions.
Thanks
Jarrett
Hi Jarret,
Thanks for pointing that out!
To be honest I only use the Refresh Date because my reports are scheduled just once a day. However if your requirement is different, you could also consider these options:
-
Account for the time difference in the Service by using the literal #duration, this will add 2 hours for example [Last Refresh] + #duration(0, 2, 0, 0)
-
Or add a Local TimeZone column, same example 2 hour difference DateTime.AddZone( [Last Refresh], 2)
let Source = DateTime.FixedLocalNow(), #"Converted to Table" = #table(1, {{Source}}), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "Last Refresh"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Last Refresh", type datetime}}), #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([Last Refresh]), type date), #"Insert Time" = Table.AddColumn(#"Inserted Date", "Time", each DateTime.Time([Last Refresh]), type time), #"Added Custom" = Table.AddColumn(#"Insert Time", "Local Timezone", each DateTime.AddZone( [Last Refresh], 2), type datetimezone ) in #"Added Custom"
This query for example will result in a table like this:
1 Like
Melissa,
Thanks for the great info! Working great now! I’m located in Eastern Time Zone, which is 4 hours behind UTC. Here is the M code I used to get the correct Eastern Time:
let
Source = DateTime.FixedLocalNow(),
#"Converted to Table" = #table(1, {{Source}}),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "Last Refresh"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Last Refresh", type datetime}}),
#"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([Last Refresh]), type date),
#"Insert Time" = Table.AddColumn(#"Inserted Date", "Time", each DateTime.Time([Last Refresh]), type time),
#"Added Custom" = Table.AddColumn(#"Insert Time", "Local Timezone", each DateTime.AddZone( [Last Refresh], 4), type datetimezone )
in
#"Added Custom"
Jarrett
3 Likes
This code is now a fixture in all my reports. However, I’m wondering whether one additional enhancement is possible - I would love to be able to capture the duration of the refresh. The “Last Refreshed” field captures the start date/time when the refresh is initiated. Is there any way to capture the date/time when the refresh is completed, so that the difference can be computed?
I’ve searched all my usual sources for how to do this, and came up empty so I’m thinking it might not be possible, but I wanted to see if you had another M trick up your sleeve to capture this info?
Thanks!
- Brian
Hi Brian,
Wish that was possible… however there is an alternative you can explore.
I hope this is helpful.
2 Likes
Hi @BrianJ,
I like to be proven wrong now and again. Just came across this article, thought I would share it with you.
.
Also thought I would mentions Query Diagnostics here.
While Query Diagnostics is no longer in preview as of March 2020, you can expect to see more improvements and information about engine performance, firewall activity, and configurable outputs added to query diagnostics in future.
.
and link to Monky Tools because this includes a TimeSleuth (to benchmark query refresh times)
Think we pretty much got all possibilities available at this time covered now…
1 Like
Most of my data comes from sql server and I retrieve them in power bi using sql Views. In all my views command i add a “getdate() as LastUpdated” column. when the power bi refreshes data it updates with the last time the data was fetched in the model which I then display in a card.
Saves me a lot of time and effort and is fairly straightforward. Of-course I fully understand this solution may not apply to everyone but thought i will share it here.
thank you.
1 Like
Thanks so much for the updated, detailed response - really helpful! I’m running my queries as we speak through the Monkey Tools Time Sleuth analyzer, which may end up being exactly what I needed.
- Brian
Hi Melissa and Jarrett,
This M code has been particularly good.
Using this one, I’ve successfullly added the last refresh query with our local time zone( Australian eastern daylight time), and it worked perfectly fine in the power bi desktop.
The refresh time was displayed correctly in the web report too when it was initially published.
However, I’ve been encountering an issue that when this report dataset was refreshed in the web, the last refresh time displayed on the report has gone back UTC time, instead of taking the local time I’ve set up using the M code.
I’ve tried to troubleshoot this myself by changing the language of the web browser. But it still doesn’t work.
Could you please kindly advise if there is any potential troubleshooting for this issue?
Hi @LOLA,
Welcome to the Forum!
As a general best practice please avoid asking questions in Solved threads, instead create a new topic and refer to the related one. This ensures that yous question is ‘visible’ for members providing support.
Can you see if the links provided in this topic help you resolve it.
All the best.
Hi Thanks.
I also created a last modified date (which is of course different to a refresh date). I did this by taking the modified date from the shared folder where all my data resides. Very handy and recommend it.
2 Likes
I just pop this into a blank query
let
Source = #table(type table[LastRefresh=datetime], {{DateTime.LocalNow()}})
in
Source
3 Likes
Even neater 
1 Like
Melissa - you’ve saved me again with #duration(0, 2, 0, 0) (or in my case (0,7,0,0). Thanks a bundle.
2 Likes