Last Refreshed Date

So I am trying to automatically show the last data refresh but the catch is I need it to always be one day behind the current date. Below is what I am using in the Advanced Query Editor.

= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMlSKjQUA”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#“Last Refresh Date” = _t])

This gives me the output seen in the below screenshot:

But I want it to always minus 1 full day. So it would show 7/22/2019 instead of 7/23/2019. The reason is our data warehouse refreshes every night at midnight so the data is always one full business day behind.

What if you try using

Date.AddDays( your date time column, -1)

Enterprise%20DNA%20Expert%20-%20Small

That did work and then I also used the below. I think the only thing that is going to be imperfect is that we do not refresh the data on the weekends and I dont know if this will continue to update even though there has not been a refresh to the data. I would assume it remains static until the data is refresh or the report re-published then it grabs that time based on the M Query?

let
    Source = Web.Page(Web.Contents("http://localtimes.info/North_America/United_States/Florida/Miami/")),
    Data1 = Source{1}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data1,{{"Column1", type text}, {"Column2", type text}}),
    date = #"Changed Type"{1}[Column2],
    time=#"Changed Type"{0}[Column2],
    datetime=DateTime.FromText(date&" "&time),
    #"Converted to Table" = #table(1, {{datetime}}),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Todays Date"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Previous Day", each Date.AddDays([Todays Date], -1))
in
    #"Added Custom"

You are correct. It won’t “pick up” the new date till it is told to do so.

Enterprise%20DNA%20Expert%20-%20Small

1 Like

Thanks to the contributors of this post. Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread.