Timestamp to Date

[EmaVasileva]

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,

[EmaVasileva · Accepted solution]

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.

[v-frfei-msft · Accepted solution]

Hi @EmaVasileva ,

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

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"

[amitchandak]

@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...

[EmaVasileva · Accepted solution]

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.

[amitchandak]

@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/

[EmaVasileva]

Hi @amitchandak 

Thank you very much. It was resolved!