Forum Discussion

AnandNamburi's avatar
AnandNamburi
Helper III
5 years ago
Solved

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,                ...
Need Help
Show and Tell
Tips and Tricks
  • v-lili6-msft's avatar
    v-lili6-msft
    5 years ago

    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's avatar
lbendlin
Super User
5 years ago

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.