Forum Discussion
JSON to Power BI table
Hello, I am attempting import JSON data from online APIs into Power BI. It all goes rather smoothly until I get to this point: when I click to expand the 'data' column, Extract Val...
Please provide full URL to your JSON file path here. I don't see where is it on link you've provided. I will check it. It is good to check if rows which have Data equals Record are needer or maybe you can exclude them.
Please check this code. You can create a blank query in Power BI and paste this code there. Then you can check.
let Source = Json.Document(Web.Contents("https://fingertips.phe.org.uk/api/latest_data/all_indicators_in_multiple_profile_groups_for_multiple_areas?group_ids=1000105%201000101%2C%201000102%2C%201000103%2C%201000104%2C%201938132733&area_type_id=102&area_codes=E09000001%2C%20E09000002%2C%20E09000003%2C%20E09000004%2C%20E09000005%2C%20E09000006%2C%20E09000007%2C%20E09000008%2C%20E09000009%2C%20E09000010%2C%20E09000011%2C%20E09000012%2C%20E09000013%2C%20E09000014%2C%20E09000015%2C%20E09000016%2C%20E09000017%2C%20E09000018%2C%20E09000019%2C%20E09000020%2C%20E09000021%2C%20E09000022%2C%20E09000023%2C%20E09000024%2C%20E09000025%2C%20E09000026%2C%20E09000027%2C%20E09000028%2C%20E09000029%2C%20E09000030%2C%20E09000031%2C%20E09000032%2C%20E09000033&include_time_periods=yes")), #"Converted to Table" = Record.ToTable(Source), Value = #"Converted to Table"{3}[Value], #"Converted to Table1" = Record.ToTable(Value), #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table1", "Value"), #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"StateSex", "Sex", "Age", "ComparatorMethodId", "SigLevel", "Periods", "IID", "Sig", "Data"}, {"Value.StateSex", "Value.Sex", "Value.Age", "Value.ComparatorMethodId", "Value.SigLevel", "Value.Periods", "Value.IID", "Value.Sig", "Value.Data"}), #"Expanded Value.Data" = Table.ExpandListColumn(#"Expanded Value1", "Value.Data"), #"Expanded Value.Data1" = Table.ExpandRecordColumn(#"Expanded Value.Data", "Value.Data", {"LoCI", "UpCI", "LoCIF", "UpCIF", "Val", "ValF", "Count"}, {"Value.Data.LoCI", "Value.Data.UpCI", "Value.Data.LoCIF", "Value.Data.UpCIF", "Value.Data.Val", "Value.Data.ValF", "Value.Data.Count"}), #"Expanded Value.Sex" = Table.ExpandRecordColumn(#"Expanded Value.Data1", "Value.Sex", {"Id", "Name"}, {"Value.Sex.Id", "Value.Sex.Name"}), #"Expanded Value.Age" = Table.ExpandRecordColumn(#"Expanded Value.Sex", "Value.Age", {"Id", "Name"}, {"Value.Age.Id", "Value.Age.Name"}), #"Expanded Value.Periods" = Table.ExpandListColumn(#"Expanded Value.Age", "Value.Periods"), #"Expanded Value.Sig" = Table.ExpandRecordColumn(#"Expanded Value.Periods", "Value.Sig", {"E12000007", "E92000001"}, {"Value.Sig.E12000007", "Value.Sig.E92000001"}), #"Expanded Value.Sig.E12000007" = Table.ExpandListColumn(#"Expanded Value.Sig", "Value.Sig.E12000007"), #"Expanded Value.Sig.E92000001" = Table.ExpandListColumn(#"Expanded Value.Sig.E12000007", "Value.Sig.E92000001") in #"Expanded Value.Sig.E92000001"