Forum Discussion
Last Refresh date/time from Power Service in Power BI Desktop report
Could you just create a custom column in Power Query and use:
DateTime.LocalNow()#"Added Custom" = Table.AddColumn(#"Changed Type", "LastUpdate", each DateTime.LocalNow())
Then format it as DateTime
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"LastUpdate", type datetime}})
This should then show wich time the table was refreshed as it will update the column with current date and time.
M code:let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpMSlaK1YlWMkJiGyOxTWDsWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dummy = _t, Abc = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Dummy", Int64.Type}, {"Abc", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "LastUpdate", each DateTime.LocalNow()),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"LastUpdate", type datetime}})
in
#"Changed Type1"Marius
amitchandak
That solution only works when you refresh the report manually. I'm looking to grab the scheduled refresh from the dataset in the service and bring it into the desktop report.
Could you just create a custom column in Power Query and use:
DateTime.LocalNow()
#"Added Custom" = Table.AddColumn(#"Changed Type", "LastUpdate", each DateTime.LocalNow())
Then format it as DateTime
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"LastUpdate", type datetime}})
This should then show wich time the table was refreshed as it will update the column with current date and time.
M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpMSlaK1YlWMkJiGyOxTWDsWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dummy = _t, Abc = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Dummy", Int64.Type}, {"Abc", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "LastUpdate", each DateTime.LocalNow()),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"LastUpdate", type datetime}})
in
#"Changed Type1"
Marius
I did have to make one change:
#"Added Custom" = Table.AddColumn(#"Changed Type", "LastUpdate", each DateTime.LocalNow() - #duration(0,4,0,0))Or do you want to make a own report that grabs all scheduled refresh history from service?
Then you might need to use this rest api:
https://docs.microsoft.com/en-us/rest/api/power-bi/admin/get-refreshables-for-capacity
If you ex use Powert Automate or ADF you can then get all history of refreshes and put it in a table on your datawarehouse or in a csv / excel file.
And then you can use this in your report as source.
MariusIt looks like this just gives me the refresh when I manually refresh the report. Does this give me the refresh from the scheduled refresh on the service?
Yes, this will also update the column when you have a scheduled refresh on the service. The column will get updatet data everytime the table is refreshed.
You might need to adjust it for your timezone, because I think its UTC that is standard in the service. But if you live in UTC+2 then you just add 2 hours:
DateTime.LocalNow() + #duration(0,2,0,0)Marius
FWIW, this table reports the correct refresh time at my desktop but DOES NOT reflect the correct refresh time when loaded to the service:
let
Source = #table(
{"Last Refresh Date"},
{{DateTimeZone.SwitchZone(DateTimeZone.UtcNow(), -6)}}
),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Last Refresh Date", type datetime}})
in
#"Changed Type"
