cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
azzlack Visitor
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.

Capture.PNG

What i want to achieve is to get the Entries items as columns, like below.

Capture2.PNG

 

Ultimately this is what I want to end up with:

Capture3.PNG

 

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Help with expanding nested json

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support Team
Community Support Team

Re: Help with expanding nested json

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.