cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TimAdams Frequent Visitor
Frequent Visitor

Creating Timestamp based on FixedNow()

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
TimAdams Frequent Visitor
Frequent Visitor

Re: Creating Timestamp based on FixedNow()

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

6 REPLIES 6
Super User
Super User

Re: Creating Timestamp based on FixedNow()

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.

 

 

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

TimAdams Frequent Visitor
Frequent Visitor

Re: Creating Timestamp based on FixedNow()

Its more, how do I create the timestamp as it is not part of the date column types

Super User
Super User

Re: Creating Timestamp based on FixedNow()

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?


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

TimAdams Frequent Visitor
Frequent Visitor

Re: Creating Timestamp based on FixedNow()

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.

Super User
Super User

Re: Creating Timestamp based on FixedNow()

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 book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
TimAdams Frequent Visitor
Frequent Visitor

Re: Creating Timestamp based on FixedNow()

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

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 366 members 4,034 guests
Please welcome our newest community members: