JSON to Power BI table

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"