Access to resource is forbidden for open API & web data

[stephaniekeen]

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/2ced9a3a2462432a92c31226e3cd3aa5_0/api

to generate the query URL in the upper right hand side, then using that URL as a Web data source. Unfortunately, I keep getting a message that says Expression.Error: Access to the resource is forbidden.

The gov.uk API portal (https://www.api.gov.uk/ons/open-geography-portal/#open-geography-portal) says it is free and open access.

I've watched this old RADACAD video at https://www.youtube.com/watch?v=a0FqNLI0VsQ which involves connecting to the JSON file at https://jsonplaceholder.typicode.com/posts and although the video does it without any trouble, I am generating the same error as above.

I also get the same error when I try and pull in data from Wikipedia as per this tutorial: https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-tutorial-importing-and-analyzing-dat...

I've cleared the Power BI cache, tried to edit Permissions under Data Source Settings (although they should all be something that can be accessed anonymously) and I'm making no progress - I'd really appreciate any advice or suggestions you can give me!

[Anonymous]

Hi,

Thanks for the solution @shafiz_p  offered, and i want to offer some more information for user to refer to.

hello @stephaniekeen ,after my testing, the open source can be connected successfully by  anonymous access,

please check whether your desktop version is the latest, the latest version is 2.131.1203.0, if your version is not the latest, you can download it from the website, then after installing it, try to reconnect the source.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[shafiz_p]

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_Liv...",

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]

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]

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]

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

[shafiz_p]

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!!