JSON to Power BI table

Question

Hello,&nbsp;&nbsp;I am attempting import JSON data from online APIs into Power BI. It all goes rather smoothly until I get to this point:&nbsp;&nbsp;&nbsp;when I click to expand the 'data' column, Extract Values yields this error:&nbsp;&nbsp;And Expand to New Rows creates 12 rows for each record. More specifically, it assigns the data values all years involved to each of the years. i.e. 2004 will have values for 2004, 2005, 2006, 2007 etc.&nbsp;Can anyone help me get around this and return only the value specific to each year?&nbsp;&nbsp;Many thanks,&nbsp;Marghe&nbsp;

anonymous · Answer

HI&nbsp;Marghe,
&nbsp;
What data you stored in column as the formula of list ?
Can you l please share some detail contents of your data and the expected result.
&nbsp;
REgards,
Xiaoxin Sheng

marghe · Answer

Hi Anonymous!&nbsp;thanks for your reply! the data I'm trying to extract is the one you can find in this link Data.When imported into power bi through APIs, the 'Data' column list should contain all columns to the right of the 'value' column in the excel spreadsheet.&nbsp;Thank you for your help!&nbsp;Marghe&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

zoloturu · Answer

Marghe,&nbsp;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.

marghe · Answer

zoloturu&nbsp;here is the APis path to my JSON file data.&nbsp;Thank you

zoloturu · Answer

Marghe,&nbsp;Please check this code. You can create a blank query in Power BI and paste this code there. Then you can check.&nbsp;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&amp;area_type_id=102&amp;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&amp;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"

Forum Discussion

JSON to Power BI table

10 Replies

Related content

Error ingesting json files in warehouse table

JSON into power BI

ELT JSON / Semi structured

JSON Theme Tables and Matrixes

Power BI table dynamically expand JSON