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.
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,
Solved! Go to 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.
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"
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"
Hope you creating in Edit query/Data tranformation mode
Custom Column not new column
Refer:
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.
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/
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |