Adding a Last Refresh date to your Report

Power Query M Code Showcase
1

Hi everyone,

To make it easy to find here’s a Power Query M query to generate a “Last refresh” table.

And if you want to give that a try, go to Power Query, select New Source, Blank Query, open the Advanced Editor (select everything) and past this in:

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)
in
  #"Insert Time"

.
The result will be a table like this and it will be updated every time the Query (Data model) is refreshed.
image

Let me admit, I created a habit of including a table like this in all my Models and Dataflows and I still find it useful today. Know that you don’t need to create any connections to this table - it’s a supporting table. You can even hide it completely in Report View!

Of course I’m aware of the fact that, with the ‘new look’ enabled, a report refresh date will be shown in the Power BI Service too BUT what if my Dataflow failed and my Report didn’t? Who’s to know?

Another plus, in my opinion, is that you can reference this date instead of using the TODAY() function. Same reason really, if the refresh fails the DAX function doesn’t know or care… That function has only one job and is pretty good at it - you’ve guessed it! - returning today’s date.

So how do you reference this Last Refresh date? Easy, just create a simple measure something like:

Last Refresh Date = 
        MAX( Last Refresh[Date] )

And now you can reference this [Last Refresh Date] measure instead of using TODAY()

.

There’s also video on creating a Last Refresh table in Power Query that includes how to deal with Timezones, you can find that here.

Big thanks to @JarrettM for sharing it with the community!

.
Let me know if you’ve enjoyed this. :heart:

13 Likes
2

Just adding some alternative search terms:
last refreshed date, last data refresh, scheduled refresh

3

@Melissa,

Bravo! I include this on all my reports for exactly the same reasons. Was just getting ready to add it to a current one I’m working on, and each time I have to go searching for the code to do it. Great having it here always easily accessible, with such a clear explanation.

  • Brian
2 Likes
4

@Melissa

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
5

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
6

@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
7

:+1:

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

8

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.

https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=8&ved=2ahUKEwii_beS9PDoAhWPhOAKHYhdDBMQwqsBMAd6BAgJEAQ&url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D2kmFfbOeFJg&usg=AOvVaw28YzlS3GLvk_ScJybMqEeV

Thanks
Jarrett

9

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:
image

1 Like
10

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
11

@Melissa,

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
12

Hi Brian,

Wish that was possible… however there is an alternative you can explore.
I hope this is helpful.

2 Likes
13

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
14

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
15

@Melissa,

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
16

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?

17

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.

18

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
19

I just pop this into a blank query

let
Source = #table(type table[LastRefresh=datetime], {{DateTime.LocalNow()}})
in
Source

3 Likes
20

Even neater :slight_smile:

1 Like