Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |