Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have created a "Last Data Refreshed" table using the query below:
let
LastRefresh = DateTime.From(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10)),
ConvertedtoTable = #table(1, {{LastRefresh}}),
In
ConvertedtoTable
How can I create a timestamp column based on LastRefresh?
Solved! Go to Solution.
I have got it working using your logic as follows:
let
LastRefresh = DateTime.From(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10)),
EpochDate= #datetime(1970,1,1,0,0,0),
TimeStampWorking=LastRefresh-EpochDate,
ConvertedtoTable = #table(3, {{LastRefresh,EpochDate,TimeStampWorking}}),
#"Renamed Columns" = Table.RenameColumns(ConvertedtoTable,{{"Column1", "Refresh Date"}, {"Column3", "TimeStamp Working"}}),
#"Inserted Total Seconds" = Table.AddColumn(#"Renamed Columns", "Total Seconds", each Duration.TotalSeconds([TimeStamp Working]), type number),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Total Seconds",{{"Total Seconds", "Timestamp"}})
in
#"Renamed Columns1"
Thanks for your help
If I understand what you are going for, you could turn it into a function like this:
let fnLastRefresh = () => let LastRefresh = DateTime.From(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10)), ConvertedtoTable = #table(1, {{LastRefresh}}) in ConvertedtoTable in fnLastRefresh
Then just create a custom column based on this function and expand the table.
Its more, how do I create the timestamp as it is not part of the date column types
How about you provide an example of the output that you are looking for? Because I apparently don't get what you are talking about, my converted time has a date and time like "1/26/2019 9:55:18 AM" which seems like a timestamp to me but apparently not?
Sorry I am talking about a SQL Timestamp, which should look something like this - 400989863
I did see alot of threads about comnverting a timestamp to a datetime. but not vice versa.
Back in the day I created a measure to convert Unix time to UTC time. I looked like this:
UTCTime = VAR UnixDays = MAX([UnixTime])/(60*60*24) RETURN (DATEVALUE("1/1/1970")+UnixDays)
So, to convert it back from UTC Time to Unix time, you would do this:
Measure = VAR __utc = [UTCTime] RETURN (__utc - DATEVALUE("1/1/1970"))*60*60*24
So, you would need to know your "epoch" date. For Unix it is 1/1/1970 and it varies depending on which SQL Server flavor you are using. Which version of SQL are you using? MySQL, Oracle, SQL Server, Postgres?
I have got it working using your logic as follows:
let
LastRefresh = DateTime.From(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10)),
EpochDate= #datetime(1970,1,1,0,0,0),
TimeStampWorking=LastRefresh-EpochDate,
ConvertedtoTable = #table(3, {{LastRefresh,EpochDate,TimeStampWorking}}),
#"Renamed Columns" = Table.RenameColumns(ConvertedtoTable,{{"Column1", "Refresh Date"}, {"Column3", "TimeStamp Working"}}),
#"Inserted Total Seconds" = Table.AddColumn(#"Renamed Columns", "Total Seconds", each Duration.TotalSeconds([TimeStamp Working]), type number),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Total Seconds",{{"Total Seconds", "Timestamp"}})
in
#"Renamed Columns1"
Thanks for your help
User | Count |
---|---|
16 | |
11 | |
5 | |
4 | |
3 |