JSON to Power BI table

[Marghe]

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 Values yields this error: 

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. 

Can anyone help me get around this and return only the value specific to each year? 

Many thanks, 

Marghe 

[v-shex-msft]

HI @Marghe,

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.

REgards,

Xiaoxin Sheng

[Marghe]

Hi @v-shex-msft!

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.

Thank you for your help!

Marghe

[zoloturu]

@Marghe,

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]

@zoloturu

here is the APis path to my JSON file data.

Thank you

[zoloturu]

@Marghe,

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"

[Marghe]

@zoloturu

thank you!!!

I still have the same problem though. There are too many rows. it yields lots of dublicate identical rows.

[zoloturu]

@Marghe,

Is my script working for you? Can you give a screenshot of issue mentioned now? 

[Marghe]

@zoloturu it does work, it produces the result below. Which has 900 rows for what should be one.

[mangle]

@Marghe did you manage to get this issue with Fingertips data solved? I'm facing the same thing.

[Marghe]

@mangle i did yes.. took a while and not the most straightforward of things.. but here is the process that works for me:

1.	Open new Power BI document and select Query Editor
2.	On Query Editor, click on New Source, then Web and paste the link obtained from Fingertips
3.	The data will look something like this: Your only option is to select ‘Transform into Table’ on the top left corner of the home ribbon
4.	Rename the table to Original_Data
5.	Click on the double arrow square sign on the top right corner of the Value column in the table
6.	A drop down will appear with all the columns for the data, select: Data, Comparator Value, Periods, IID, Sex and Age
7.	Now, the order of the next few steps is crucial
8.	Click on the double arrow sign on data and expand it. This will create as many columns as areas. Plus one named ‘Value’
9.	While they are still selected, right click on any of them and select ‘Unpivot Only selected columns’
10.	Right click on the ‘Value’ column and select ‘Add as new query’.
11.	Right click on the ‘ComparatorValue’ column and select ‘Add as new query’
12	Expand ‘periods’ to new rows
13.	Expand sex and age
14.	Click on the tiny table symbol in the top left corner of the table and select ‘Add Index Column’ starting from 1
15.	Go to the Comparator Value query and expand it – will come up with two columns 1 and 4. 1 = London, 4= England. It’s a good idea to rename them
16.	Click on the tiny table symbol in the top left corner of the table and select ‘Add Index Column’ starting from 1
17.	Go to the Value query and expand it. Rename the columns something meaningful
18.	Click on the tiny table symbol in the top left corner of the table and select ‘Add Index Column’ starting from 1
19.	Go back to the original_data query and in the Home ribbon, top right corner select the drop down from merge queries.
20.	In the window that will appear merge the original data query with either of the two through a left join on the Index column (one at a time)
21.	Now expand the columns from the merged queries (no need to expand the index column from them)