Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |