Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |