How to retrieve data from recursive JOSN

[AnandNamburi]

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

[v-lili6-msft · Accepted solution]

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

[lbendlin]

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.