Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
Hi,
I'm totally new to Power BI so I'm tried different approached to convert my JSON file to a table but unsuccessful so far.
This is my JSON File
{ "Family": [ { "Father": "F1", "Age": 50, "Mother": "M1", "MAge": 49, "Children": [ { "Name": "C1" }, { "Name": "C2" } ] }, { "Father": "F2", "Age": 55, "Mother": "M2", "MAge": 53, "Children": [ { "Name": "Cc1" }, { "Name": "Cc2" } ] } ] }
I want this to be a table like below
Father Age Mother MAge F1 50 M1 49 F2 55 M2 53
I tried like convert table and transpose which is not working I always getting error like Expression.Error: We cannot convert a value of type Record to type
Please help me to get solve this
Thanks !
Jay
Solved! Go to Solution.
You can use this
let Source = Json.Document(File.Contents("C:\Users\hp\Desktop\File.JSON")), Family = Source[Family], #"Converted to Table" = Table.FromList(Family, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Father", "Age", "Mother", "MAge", "Children"}, {"Father", "Age", "Mother", "MAge", "Children"}) in #"Expanded Column1"
Please see attached files as well
Hi Jay,
Since you have a standard JSON file, I would suggest you use the built-in connector JSON and follow the steps one by one. The underlying code will be generated by the Query Editor automatically.
Best Regards,
You can use this
let Source = Json.Document(File.Contents("C:\Users\hp\Desktop\File.JSON")), Family = Source[Family], #"Converted to Table" = Table.FromList(Family, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Father", "Age", "Mother", "MAge", "Children"}, {"Father", "Age", "Mother", "MAge", "Children"}) in #"Expanded Column1"
Please see attached files as well
Thanks for the solution, Actually I'm missing the ExpandRecordColumn Function