Forum Discussion
Extract values from Json
Hello,
I am trying to retrieve data from an API that sends back this type of JSON :
{
"dataframes": [
{
"data": {
"Timestamp": [
"2024-12-31T23:00:00.000+00:00",
"2025-01-31T23:00:00.000+00:00",
"2025-02-28T23:00:00.000+00:00"
],
"signal1": [
6679,
3348,
469
],
"signal2": [
0,
0,
0
]
}
},
{
"data": {
"Timestamp": [
"2025-03-31T22:00:00.000+00:00",
"2025-04-30T22:00:00.000+00:00",
"2025-05-31T22:00:00.000+00:00",
"2025-06-30T22:00:00.000+00:00",
"2025-07-31T22:00:00.000+00:00",
"2025-08-31T22:00:00.000+00:00",
"2025-09-30T22:00:00.000+00:00"
],
"signal1": [
3936,
2392,
1939,
3016,
2151,
2698,
5454
],
"signal2": [
0,
0,
0,
0,
36,
60,
82
]
}
}
]
}The issue is that in power query I am not able to retrieve each signals corresponding to each timestamp. Everytime, i expand the column timestamps and unpivot the columns, i have every signals for 1 timestamp :
After expanding the timestamps, unpivoting the columns and before expanding the values :
After expanding the values :
I'd like to have 1 line/timestamp/signals, how can I expand everything at the same time so I have the right values in front of the right timestamp ?
Thank you in advance,
MD
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"
6 Replies
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 ?
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"
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!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.
