Card to show Refresh Time when data is updated via Flow

[KG1]

Hi

My data source is connected to excel files stored in a sharepoint folder.

The reports are set up to refresh everytime the excel files are updated using Microsoft Flow (Power automate)

How do I add a card to the report show the date and time the report was refreshed?

Thank you in advance

[harshnathani]

Hi @KG1 ,

See if these help.

https://www.youtube.com/watch?v=oN6mOmEruOQ

https://docs.microsoft.com/en-us/azure/devops/report/powerbi/add-last-refresh-time?view=azure-devops

https://www.kasperonbi.com/show-the-refresh-datetime-in-a-power-bi-report-and-dashboard/

https://www.enhansoft.com/how-to-add-the-last-refreshed-date-and-time-to-a-power-bi-report/

Regards,

HN

[edhans]

Hi @KG1 - this is a two step process.

1) Capture the refresh time in Power Query. Use this M code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZDLCQAhDAV78Sxonv9axP7bWLNPMN4mDMYhc7oUBAFRmvNOJBTy8r8RnTpNJh8TdRo0iUzT94BIIeTzRBdAaBr5GF0A0FTyMZqGdNM2mwDkG7CZZuhQKEA2ZdWI+pQ1U9ae/7v5v9vTYNzTYNiyFG/Z5rU+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [dtDSTStart = _t, dtDSTEnd = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"dtDSTStart", type date}, {"dtDSTEnd", type date}}),
    varCurrentDate = DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-8)),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [dtDSTStart] < varCurrentDate and [dtDSTEnd] > varCurrentDate),
    varDSTOffset = Table.RowCount(#"Filtered Rows"),
    #"Last Refresh Date" = #table(
        type table
            [
                #"RefreshDate"=datetimezone
            ],
        {
            {DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-8 + varDSTOffset,0)}
        }
        )
in
    #"Last Refresh Date"

That code seems a bit complex for the time stamp, but it will dynamically adjust for daylight savings time. Otherwise your refresh times will be 1hr off half of the year if you observe DST. 

You need to change those -8 values to your UTC offset. -5 for Eastern, 0 for UTC, etc. 

In Power Query, you can also see the DST Table I am using, which is for the US. Click the Gear icon next ot the Source line of the query. Edit that table to your region.

2) Next, this will get loaded into DAX. Name the Query in Power Query [Refresh Time Stamp] then use this measure:

Last Update = "Last Update on " & UNICHAR(10) & FORMAT(MAX('Refresh Time Stamp'[RefreshDate]),"MMM DD, YYYY H:MM AM/PM")

That will return a 2 line update. You can change the text, or get rid of the linefeed (unichar(10)) if you want. Drop that in your card.

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[KG1]

Hi @edhans 

Thank you very much for your quick reply

I have an error though

Going back through the steps it appears to be an error with the date format in rows 5, 6 and 11

Step 1

Step 2

Filtered Rows Error

Have I done something incorrectly?

Thank you

[edhans · Accepted solution]

Hrm... oops. This is a culture issue.

Use this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZDLCQAhDAV78Sxonv9axP7bWLNPMN4mDMYhc7oUBAFRmvNOJBTy8r8RnTpNJh8TdRo0iUzT94BIIeTzRBdAaBr5GF0A0FTyMZqGdNM2mwDkG7CZZuhQKEA2ZdWI+pQ1U9ae/7v5v9vTYNzTYNiyFG/Z5rU+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [dtDSTStart = _t, dtDSTEnd = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source, {{"dtDSTStart", type date}, {"dtDSTEnd", type date}}, "en-US"),
    varCurrentDate = DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-8)),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [dtDSTStart] < varCurrentDate and [dtDSTEnd] > varCurrentDate),
    varDSTOffset = Table.RowCount(#"Filtered Rows"),
    #"Last Refresh Date" = #table(
        type table
            [
                #"RefreshDate"=datetimezone
            ],
        {
            {DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-8 + varDSTOffset,0)}
        }
        )
in
    #"Last Refresh Date"

Those are in the US Date format. Glad you caught that error for me.

You can also see this blog about it.

[KG1]

@edhans 

Thank you  - I have created the card I wanted

The data refresh isnt until the morning so will post back then whether its worked correctly

Thanks again

[edhans]

Excellent @KG1 - be sure to mark one of these answers as the solution once you have verified.

Of course, you could test it by forcing a refresh if you want to be sure. 

[KG1]

@edhans   - it worked perfectly

Thank you very much for your help again 🙂

[edhans]

Excellent @KG1 - glad I was able to assist.