Transform Data, convert JSON (list) to rows

[ccarreno]

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

[amitchandak · Accepted solution]

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