Forum Discussion
Solved
Transform Data, convert JSON (list) to rows
I'm trying to take a cell in Power BI's Transform Data, which contains the following information:
"{""1727568000000:0"", ""1727215200000:0"", ""1727294400000:0"", ...}"
and I want each key-value pair to be in two columns, one with the timestamp and the other with the value, and also make them final
Example:
| TimeStamp | Value |
| 1727568000000 | 0 |
| 1727294400000 | 1 |
ccarreno , I doubt this is a JSON format power bi can detect. Try this power query code in blank query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wqo5RilEyNDcyNzWzMAADKwOQkI4CTMLI0NQIu4SliQmSRK1SbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","""""","",Replacer.ReplaceText,{"Column1"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","{","",Replacer.ReplaceText,{"Column1"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","}","",Replacer.ReplaceText,{"Column1"}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value2", {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1", type text}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Column1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column1.1", "Column1.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1", Text.Type}, {"Column1.2", Int64.Type}}) in #"Changed Type2"
1 Reply
ccarreno , I doubt this is a JSON format power bi can detect. Try this power query code in blank query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wqo5RilEyNDcyNzWzMAADKwOQkI4CTMLI0NQIu4SliQmSRK1SbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","""""","",Replacer.ReplaceText,{"Column1"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","{","",Replacer.ReplaceText,{"Column1"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","}","",Replacer.ReplaceText,{"Column1"}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value2", {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1", type text}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Column1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column1.1", "Column1.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1", Text.Type}, {"Column1.2", Int64.Type}}) in #"Changed Type2"