Forum Discussion
azzlack
8 years agoRegular Visitor
Help with expanding nested json
Consider a list of the following json objects:
{
"_id": "5a79099b662a2f270000223d",
"date": "2018-02-07",
"customer": {
"_id": "59efa21dad33ca6d00001c8f",
"name": "Ove Andersen"
},
"data": {
"Entries": [
{
"type": "Recommended",
"menuAmount": 2,
"menuExtraLarge": false
},
{
"type": "Salad",
"menuAmount": 3,
"menuExtraLarge": false
}
]
}
}Please note that the Entries property might be empty.
This is how my query result looks like at the moment.
What i want to achieve is to get the Entries items as columns, like below.
Ultimately this is what I want to end up with:
This is the code that I currently have:
let
#"Added Data" = Table.AddColumn(#"Added Skip", "Data", each Json.Document(Web.Contents("--REDACTED--"))),
#"Expanded Data" = Table.ExpandListColumn(#"Added Data", "Data"),
#"Expanded Data1" = Table.ExpandRecordColumn(#"Expanded Data", "Data", {"_id", "customer", "date", "data"}, {"Data._id", "Data.customer", "Data.date", "Data.data"}),
#"Expanded Data.customer" = Table.ExpandListColumn(#"Expanded Data1", "Data.customer"),
#"Expanded Data.customer1" = Table.ExpandRecordColumn(#"Expanded Data.customer", "Data.customer", {"_id"}, {"Data.customer._id"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Data.customer1",{{"Data.date", type datetimezone}}),
#"d" = Table.ExpandRecordColumn(#"Changed Type", "Data.data", {"Entries"}, {"Data.data.Entries"})
in
#"d"
Any experts out there that know how to achieve this?
Hi azzlack,
Here are two articles describe how to deal with nested JSON value:
Nested JSON and never end Records
How to deal JSON with Power BI Desktop
Regards,
Yuliana Gu
1 Reply
- v-yulgu-msftMicrosoft Employee
Hi azzlack,
Here are two articles describe how to deal with nested JSON value:
Nested JSON and never end Records
How to deal JSON with Power BI Desktop
Regards,
Yuliana Gu