Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!See when key Fabric features will launch and what’s already live, all in one place and always up to date. Explore the new Fabric roadmap
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
Solved! Go to Solution.
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
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
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.
User | Count |
---|---|
91 | |
74 | |
71 | |
58 | |
55 |
User | Count |
---|---|
41 | |
39 | |
34 | |
32 | |
30 |