Forum Discussion
Solved
Convert JSON that contains {name: XXX; value : YYYY} into table where colum = XXX and row =YYY
Hi Folks,
I need your help concerning the import of this JSON in PowerBI using Power Query :
{
"Response": {
"TimeUnit": [
1619395200000,
1619308800000,
1619222400000,
1619136000000
],
"stats": {
"data": [
{
"identifier": {
"names": [
"developer_app",
"api_product"
],
"values": [
"XXXX",
"aaaa"
]
},
"metric": [
{
"values": [
5.0,
0.0,
6.0,
1.0
]
}
]
},
{
"identifier": {
"names": [
"developer_app",
"api_product"
],
"values": [
"YYYY",
"bbbb"
]
},
"metric": [
{
"values": [
1.0,
0.0,
0.0,
1.0
]
}
]
}
]
}
}
}
I would like to obtain from this JSON the following table :
Date | developer_app | api_product | Value |
1619395200000 | XXXX | aaaa | 5 |
1619308800000 | XXXX | aaaa | 0 |
1619222400000 | XXXX | aaaa | 6 |
1619136000000 | XXXX | aaaa | 1 |
1619395200000 | YYYY | bbbb | 1 |
1619308800000 | YYYY | bbbb | 0 |
1619222400000 | YYYY | bbbb | 0 |
1619136000000 | YYYY | bbbb | 1 |
How can I do that using power query UI and/or advanced pane for coding ?
Thanks in advance for your help.
Regards,
Hi ochaoui
I guess I made too many steps, hope you can work from it, paste in Advanced Editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZLRCsIgFIZfJbweoatG9RhRUMwR1k4gNCfTuom9e9qC1tLlRQRB/41wvt/D7/GkKbpQMTCiaAFKlkIBRfPBvdiAJS9gJbi2IH0AK5KQ2Wg2ibFV5GB4OvWxOI7HPkZGyQ3hB8qidialmVadpA3JmWaOpFaX11Jzh+cgND9wqBwtn62CFaA8/TtB4AzHUkK1ZVJSFL3zM8m3sirz095M2m/OehpRdGbHU2C+tVFILKPePG5UexpTVICu+P5NxJ4/aLoEP9RqMuwumUs4yJUEucgQ95s8Y7Oq3chxwzXk/5K3rRujgFg7o19fcvLBJQ9zfWnJn0stx52Yo0ZZdgU=", 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}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Json.Document([Column1])), #"Added Custom3" = Table.AddColumn(#"Added Custom", "Date", each [Custom][Response][TimeUnit]), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "stats", each [Custom][Response][stats][data]), #"Expanded stats" = Table.ExpandListColumn(#"Added Custom4", "stats"), #"Added Custom1" = Table.AddColumn(#"Expanded stats", "Value", each [stats][metric]{0}[values]), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "identifier", each Record.FromList( List.Zip( Record.ToList( [stats][identifier])),{"develop_app","api_product"})), #"Expanded identifier" = Table.ExpandRecordColumn(#"Added Custom2", "identifier", {"develop_app", "api_product"}, {"develop_app", "api_product"}), #"Added Custom5" = Table.AddColumn(#"Expanded identifier", "Custom.1", each List.Zip({[Date],[Value]})), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom5",{"Custom.1", "develop_app", "api_product"}), #"Expanded Custom.1" = Table.ExpandListColumn(#"Removed Other Columns", "Custom.1"), #"Expanded develop_app" = Table.ExpandListColumn(#"Expanded Custom.1", "develop_app"), #"Filtered Rows" = Table.SelectRows(#"Expanded develop_app", each ([develop_app] <> "developer_app")), #"Expanded api_product" = Table.ExpandListColumn(#"Filtered Rows", "api_product"), #"Filtered Rows1" = Table.SelectRows(#"Expanded api_product", each ([api_product] <> "api_product")), Custom1 = Table.TransformColumns( #"Filtered Rows1",{"Custom.1", each Record.FromList(_,{"Date","Value"})}), #"Expanded Custom.2" = Table.ExpandRecordColumn(Custom1, "Custom.1", {"Date", "Value"}, {"Date", "Value"}) in #"Expanded Custom.2"
3 Replies
Hi ochaoui
I guess I made too many steps, hope you can work from it, paste in Advanced Editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZLRCsIgFIZfJbweoatG9RhRUMwR1k4gNCfTuom9e9qC1tLlRQRB/41wvt/D7/GkKbpQMTCiaAFKlkIBRfPBvdiAJS9gJbi2IH0AK5KQ2Wg2ibFV5GB4OvWxOI7HPkZGyQ3hB8qidialmVadpA3JmWaOpFaX11Jzh+cgND9wqBwtn62CFaA8/TtB4AzHUkK1ZVJSFL3zM8m3sirz095M2m/OehpRdGbHU2C+tVFILKPePG5UexpTVICu+P5NxJ4/aLoEP9RqMuwumUs4yJUEucgQ95s8Y7Oq3chxwzXk/5K3rRujgFg7o19fcvLBJQ9zfWnJn0stx52Yo0ZZdgU=", 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}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Json.Document([Column1])), #"Added Custom3" = Table.AddColumn(#"Added Custom", "Date", each [Custom][Response][TimeUnit]), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "stats", each [Custom][Response][stats][data]), #"Expanded stats" = Table.ExpandListColumn(#"Added Custom4", "stats"), #"Added Custom1" = Table.AddColumn(#"Expanded stats", "Value", each [stats][metric]{0}[values]), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "identifier", each Record.FromList( List.Zip( Record.ToList( [stats][identifier])),{"develop_app","api_product"})), #"Expanded identifier" = Table.ExpandRecordColumn(#"Added Custom2", "identifier", {"develop_app", "api_product"}, {"develop_app", "api_product"}), #"Added Custom5" = Table.AddColumn(#"Expanded identifier", "Custom.1", each List.Zip({[Date],[Value]})), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom5",{"Custom.1", "develop_app", "api_product"}), #"Expanded Custom.1" = Table.ExpandListColumn(#"Removed Other Columns", "Custom.1"), #"Expanded develop_app" = Table.ExpandListColumn(#"Expanded Custom.1", "develop_app"), #"Filtered Rows" = Table.SelectRows(#"Expanded develop_app", each ([develop_app] <> "developer_app")), #"Expanded api_product" = Table.ExpandListColumn(#"Filtered Rows", "api_product"), #"Filtered Rows1" = Table.SelectRows(#"Expanded api_product", each ([api_product] <> "api_product")), Custom1 = Table.TransformColumns( #"Filtered Rows1",{"Custom.1", each Record.FromList(_,{"Date","Value"})}), #"Expanded Custom.2" = Table.ExpandRecordColumn(Custom1, "Custom.1", {"Date", "Value"}, {"Date", "Value"}) in #"Expanded Custom.2"Hi Anonymous !
Thank you for your answer ! Can you explain the steps you made to achieve this ?
And did you use the U to do it or is it full code ?
Hi ochaoui
At first I was clicking in the UI all the way down to see the data, but too many clicks! So I went back to modify some of them in the formula bar. You can paste the code in Advanced Editor then see each step on the right.
