Forum Discussion

stephaniekeen's avatar
stephaniekeen
Frequent Visitor
2 years ago

Access to resource is forbidden for open API & web data

Hi everyone, I've been tying to pull in data from the Office for National Statistics Open Geography API. I've been using this page: https://geoportal.statistics.gov.uk/datasets/2ced9a3a2462432a92c3...
Need Help
shafiz_p's avatar
shafiz_p
Icon for Super User rankSuper User
2 years ago

Hi stephaniekeen  I have used the query URL you have mention to extract json information. Please use the below code in power query to extract json data. First create a blank query and paste the below code. Tweak fields name accordingly.

let


Source = "https://services1.arcgis.com/ESMARspQHYMw9BZ9/arcgis/rest/services/Online_ONS_Postcode_Directory_Live/FeatureServer/0/query?where=1%3D1&outFields=*&outSR=4326&f=json",


data = Json.Document(Web.Contents(Source)),


features = data[features],


ConvertedtoTable = Table.FromList(
features,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),


ExpandedColumn1 = Table.ExpandRecordColumn(
ConvertedtoTable, "Column1",
{"attributes", "geometry"},
{"attributes", "geometry"}
),


ExpandedAttributes = Table.ExpandRecordColumn(
ExpandedColumn1,
"attributes",
{"OBJECTID", "PCD", "PCD2", "PCDS", "DOINTR", "DOTERM", "OSCTY", "CED", "OSLAUA", "OSWARD", "USERTYPE",
"OSEAST1M", "OSNRTH1M", "OSGRDIND", "OSHLTHAU", "NHSER", "CTRY", "RGN", "PCON", "TTWA", "PARK", "OA11",
"LSOA11", "MSOA11", "PARISH", "WZ11", "SICBL", "BUA11", "BUASD11", "RU11IND", "OAC11", "LAT", "LONG",
"LEP1", "LEP2", "PFA", "IMD", "CALNCV", "ICB", "ITL", "OA21", "LSOA21", "MSOA21"},

{"OBJECTID", "PCD", "PCD2", "PCDS", "DOINTR", "DOTERM", "OSCTY", "CED", "OSLAUA", "OSWARD", "USERTYPE",
"OSEAST1M", "OSNRTH1M", "OSGRDIND", "OSHLTHAU", "NHSER", "CTRY", "RGN", "PCON", "TTWA", "PARK", "OA11",
"LSOA11", "MSOA11", "PARISH", "WZ11", "SICBL", "BUA11", "BUASD11", "RU11IND", "OAC11", "LAT", "LONG",
"LEP1", "LEP2", "PFA", "IMD", "CALNCV", "ICB", "ITL", "OA21", "LSOA21", "MSOA21"}
),


Expandedgeometry = Table.ExpandRecordColumn(
ExpandedAttributes,
"geometry",
{"x", "y"},
{"x", "y"}
)


in
Expandedgeometry



Hope this helps!!

 

If this solved your problem, please mark it as a solution.

stephaniekeen's avatar
stephaniekeen
Frequent Visitor
2 years ago

Hi Shafiz,

Thank you for your time on this. Unfortunately I've copied your code (and replaced the URL with the full link, not the truncated version that Fabric Community displays) but I am still getting an error that says Expression.Error: Access to the resource is forbidden.

  • shafiz_p's avatar
    shafiz_p
    Icon for Super User rankSuper User
    2 years ago

    Please clear global permission from option and setting. When ask for permission, use annonymous permission. 

    You can download my file using the below link:

    Json info extract 


    Hope this helps !!

    If this solved your problem, please mark it as a solution!!

    • stephaniekeen's avatar
      stephaniekeen
      Frequent Visitor
      2 years ago

      I've done that I'm afraid, and it still says Expression.Error: Access to the resource is forbidden.

      • shafiz_p's avatar
        shafiz_p
        Icon for Super User rankSuper User
        2 years ago

        Have you tried using the uploaded file, and does it still show the same error? It is a free source and annonymous authentication works for me.

         

        Try to update power bi to the latest version. If still this problem exist, then go to the following path :

         

        "C:\Users\Username\AppData\Local\Microsoft\Power BI Desktop"

        and try renaming User.zip to User1.zip. This will clear all previous user logins caches and will create a new user folder.

        Hope this solve your problem!!

        If, please mark it as a solution!!