cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EmaVasileva
Helper IV
Helper IV

Timestamp to Date

Hey guys,

Could someone helps me to transform the following timestamp to a Normal date:
[1583829685000,56.5] 

I need a formula which I can use to create a custom column. 
I read another articles and test different fomulas, but they give me an error. I tried also the following: #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, ([UnixTime]/1000))

Thank you in advance.

Best,

2 ACCEPTED SOLUTIONS

Hey @amitchandak 

Thank you for the reference 🙂

I'm new at Power Bi, so I kindly request a little more help.

I'm getting data from a website through URL Link (console link) and I receive in power Bi Query one column with data with the following info in it: 
[1583829685000,56.5]  

1583829685000 is the timestamp and 56.5 is a metric. Any idea of a formula and steps how to add a separate columns where one column to show the timestamp as a Date and another one just do show the metric. 

Thank you.

View solution in original post

v-frfei-msft
Community Support
Community Support

Hi @EmaVasileva ,

 

I have created a sample for your reference. Please refer to the picture as below.

Capture.PNG

Btw, please refer to the M code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WijY0tTC2MLI0szA1MDDQMTXTM41VUIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","[","",Replacer.ReplaceText,{"date"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","]","",Replacer.ReplaceText,{"date"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "date", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"date.1", "date.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"date.1", Int64.Type}, {"date.2", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"date.2"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each #datetime(1970, 1, 1, 0, 0, 0)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each #duration(0, 0, 0, [date.1]/1000),type duration),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each [Custom]+[Custom.1])
in
    #"Added Custom2"

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

5 REPLIES 5
v-frfei-msft
Community Support
Community Support

Hi @EmaVasileva ,

 

I have created a sample for your reference. Please refer to the picture as below.

Capture.PNG

Btw, please refer to the M code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WijY0tTC2MLI0szA1MDDQMTXTM41VUIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","[","",Replacer.ReplaceText,{"date"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","]","",Replacer.ReplaceText,{"date"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "date", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"date.1", "date.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"date.1", Int64.Type}, {"date.2", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"date.2"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each #datetime(1970, 1, 1, 0, 0, 0)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each #duration(0, 0, 0, [date.1]/1000),type duration),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each [Custom]+[Custom.1])
in
    #"Added Custom2"

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

amitchandak
Super User
Super User

@EmaVasileva 

Hope you creating in Edit query/Data tranformation mode

Custom Column not new column

Refer:

https://community.powerbi.com/t5/Desktop/Converting-UNIX-time-to-Date-in-PowerBI-for-Desktop/td-p/13...

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Hey @amitchandak 

Thank you for the reference 🙂

I'm new at Power Bi, so I kindly request a little more help.

I'm getting data from a website through URL Link (console link) and I receive in power Bi Query one column with data with the following info in it: 
[1583829685000,56.5]  

1583829685000 is the timestamp and 56.5 is a metric. Any idea of a formula and steps how to add a separate columns where one column to show the timestamp as a Date and another one just do show the metric. 

Thank you.

View solution in original post

@EmaVasileva 

You can use

in M

https://docs.microsoft.com/en-us/powerquery-m/text-replace and replace and remove [ ]

Also,there is option in edit query to split the column based on delimiter

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Hi @amitchandak 

Thank you very much. It was resolved! 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors