Forum Discussion
mdufresne34
8 months agoFrequent Visitor
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...
- 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"
v-tejrama
8 months agoCommunity Support
Hi mdufresne34 ,
Thank you amitchandak for the helpful input!
Were you able to resolve the issue? If the response addressed your query, kindly confirm. This helps keep the community informed and improves solution visibility.
Thank you for your support!
- v-tejrama8 months agoCommunity Support
Hi mdufresne34 ,
I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.
Thank you.