Forum Discussion
KG1
Resolver I
Resolver ICard 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) ...
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.
KG1Resolver I
Resolver IHi 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
Community Champion
Community ChampionHrm... 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.