Help with expanding nested json

[azzlack]

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?

[v-yulgu-msft · Accepted 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