Forum Discussion

mdufresne34's avatar
mdufresne34
Frequent Visitor
8 months ago
Solved

Extract values from Json

Hello,   I am trying to retrieve data from an API that sends back this type of JSON :  { "dataframes": [ { "data": { "Timestamp": [ "20...
  • amitchandak's avatar
    amitchandak
    8 months ago

    mdufresne34 , Got it. After reaching the at list level. I used List.Zip and then again done few operations. You can paste the below code in advance editor of an empty query. Also attaching file after signature

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xVNNC8IwDP0r0qtW0qTtVn+Ht3WHgh8ITsR5k/13qzsMdRm0KJYdsry8vLelqSpx86dZPF5swjXsLqHZtl6sZlWffpzbEA6Vj5o3oAfXh9jhGprzW5fXKgTUUqEktUZaAcRnCQDzZ+TFYoJnJKhMHkosR3mftHqklRftYX8KRzXxadYWjnFBpEsG0talWcAJC8CIsPkR6ddUN7x2i59fjDgoeg4YkwesJUEOz2Tq2Uy9IlOvzOQ5zuf3Lj45sowLJIcMpByx6wKKbaiM4iDruCUz2ug/bllinv2blmOUmLTIfRgLOlHXdw==", 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}}),
        #"Parsed JSON" = Table.TransformColumns(#"Changed Type",{},Json.Document),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Parsed JSON", "Column1", {"dataframes"}, {"Column1.dataframes"}),
        #"Expanded Column1.dataframes" = Table.ExpandListColumn(#"Expanded Column1", "Column1.dataframes"),
        #"Expanded Column1.dataframes1" = Table.ExpandRecordColumn(#"Expanded Column1.dataframes", "Column1.dataframes", {"data"}, {"Column1.dataframes.data"}),
        #"Expanded Column1.dataframes.data" = Table.ExpandRecordColumn(#"Expanded Column1.dataframes1", "Column1.dataframes.data", {"Timestamp", "signal1", "signal2"}, {"Column1.dataframes.data.Timestamp", "Column1.dataframes.data.signal1", "Column1.dataframes.data.signal2"}),
        #"Added Custom" = Table.AddColumn(#"Expanded Column1.dataframes.data", "Custom", each List.Zip({[Column1.dataframes.data.Timestamp],[Column1.dataframes.data.signal1],[Column1.dataframes.data.signal2]})),
        #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
        #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type datetime}, {"Custom.2", Int64.Type}, {"Custom.3", Int64.Type}})
    in
        #"Changed Type1"