Forum Discussion
How to retrieve data from recursive JOSN
Hi All,
I have a JOSN file and the data in it is like below.
{
"firstName": "Bidhan",
"lastName": "Chatterjee",
"age": 40,
"address": {
"streetAddress": "144 J B Hazra Road",
"city": "Burdwan",
"state": "Paschimbanga",
"postalCode": "713102"
},
"phoneNumber": [{
"type": "personal",
"number": "09832209761"
},
{
"type": "fax",
"number": "91-342-2567692"
}
]
}
When I'm trying to retrive the data from the file I'm getting message likw below.
Can anyone help me out here, how to retrive the data from address and phone number coumns.
Thanks,
Anand
hi AnandNamburi
you could try this way to retrieve data
let Source = Json.Document(File.Contents("C:\Users\Desktop\new.json")), #"Converted to Table" = Record.ToTable(Source), #"Pivoted Column" = Table.Pivot(#"Converted to Table", List.Distinct(#"Converted to Table"[Name]), "Name", "Value"), #"Expanded address" = Table.ExpandRecordColumn(#"Pivoted Column", "address", {"streetAddress", "city", "state", "postalCode"}, {"streetAddress", "city", "state", "postalCode"}), #"Expanded phoneNumber" = Table.ExpandListColumn(#"Expanded address", "phoneNumber"), #"Expanded phoneNumber1" = Table.ExpandRecordColumn(#"Expanded phoneNumber", "phoneNumber", {"type", "number"}, {"type", "number"}) in #"Expanded phoneNumber1"Result:
Regards,
Lin
2 Replies
This is not recursive, it's just a multi level hierarchy - that's what JSON is about.
In Power Query you need to manually traverse that hierarchy and pick the fields that you want to convert into a flattened version. There's no magic "give me all the JSON data in a flat table" button, it's all manual work.
hi AnandNamburi
you could try this way to retrieve data
let Source = Json.Document(File.Contents("C:\Users\Desktop\new.json")), #"Converted to Table" = Record.ToTable(Source), #"Pivoted Column" = Table.Pivot(#"Converted to Table", List.Distinct(#"Converted to Table"[Name]), "Name", "Value"), #"Expanded address" = Table.ExpandRecordColumn(#"Pivoted Column", "address", {"streetAddress", "city", "state", "postalCode"}, {"streetAddress", "city", "state", "postalCode"}), #"Expanded phoneNumber" = Table.ExpandListColumn(#"Expanded address", "phoneNumber"), #"Expanded phoneNumber1" = Table.ExpandRecordColumn(#"Expanded phoneNumber", "phoneNumber", {"type", "number"}, {"type", "number"}) in #"Expanded phoneNumber1"Result:
Regards,
Lin
