Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
2 | |
1 | |
1 | |
1 |