Displaying Last Refresh Time in PST

[Anonymous]

Hi, after publishing my PBI report to the web, the Last Refresh = #table(type table[LastRefresh=datetime], {{DateTime.LocalNow()}}) time stamp in PBI service is showing UTC time (which is 8 hrs ahead of PST).

I looked for several solutions online, including the API code below (which I pasted in Advanced Editor) - but it gave me the error (screenshot attached). What is the easiest code to show the PST time in the report after it's published to PBI service? Thanks.

CODE:

let
Source = Web.Page(Web.Contents("http://www.timeapi.org/pst")),
Data0 = Source{0}[Data],
Children = Data0{0}[Children],
Children1 = Children{1}[Children],
#"Removed Other Columns" = Table.SelectColumns(Children1,{"Text"}),
#"Inserted Last Characters" = Table.AddColumn(#"Removed Other Columns", "Last Characters", each Text.End(Text.From([Text], "en-US"), 6), type text),
#"Changed Type2" = Table.TransformColumnTypes(#"Inserted Last Characters",{{"Text", type datetimezone}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type2",":00",".0",Replacer.ReplaceText,{"Last Characters"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",":30",".5",Replacer.ReplaceText,{"Last Characters"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value1",{{"Last Characters", type number}, {"Text", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "LastRefresh", each [Text]+#duration(0,Number.From([Last Characters]),0,0)),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"LastRefresh"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns1",{{"LastRefresh", type datetime}})
in
#"Changed Type"

[az38]

Hi @Anonymous 

if you are sure in -8 hours offset try to use

DateTimeZone.SwitchZone(DateTimeZone.LocalNow(), -8)

[Anonymous · Accepted solution]

This is what I ended up doing.

I used the original code: #table(type table[LastRefresh=datetime], {{DateTime.LocalNow()}})

Then I created a measure: Last Refresh_PST = MIN('Last Refresh'[LastRefresh])-7/24

That seemed to work after I published to PBI service.

Thanks!

[v-yiruan-msft]

Hi @Anonymous ,

Thanks for sharing your solution. Could you please mark your post as Answered? It will help others in the community find this solution easily if they face the same problem with you. Thank you.

Best Regards

Rena

[Anonymous]

It did not work. It's giving me an error but doesn't tell me what, and the visualization is (Blank). Thanks.