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...
Need Help
  • 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"
amitchandak's avatar
amitchandak
Super User
8 months ago

mdufresne34 , Please check have already tried to use it as json column 

and expand one by one 

 

mdufresne34's avatar
mdufresne34
Frequent Visitor
8 months ago

Hello, This is what i do, but the issue is when i expand the columns, how can I have the right data in front of the right timestamp ?

  • amitchandak's avatar
    amitchandak
    Super User
    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"