Forum Discussion

KG1's avatar
KG1
Icon for Resolver I rankResolver I
6 years ago
Solved

Card to show Refresh Time when data is updated via Flow

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

 

 

 

 

Need Help
  • edhans's avatar
    edhans
    6 years ago

    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.

8 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      KG1
      Icon for Resolver I rankResolver I
      6 years ago

      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's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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.