cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
seann Frequent Visitor
Frequent Visitor

404 error when loading data to model

I'm querying an API using http and am able to load and model the data in the query editor with no issues. When I click save and close, it begins to load to the model but then I receive a webcontents error 404.

I'm receiving data when querying through a browser (chrome), so there's no issue with the URL itself and the data is loading in the query editor.

Where am I going wrong?
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: 404 error when loading data to model

Yes, that's why I proposed it as a workaround 😉

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

9 REPLIES 9
Community Support Team
Community Support Team

Re: 404 error when loading data to model

Hi @seann,

 

Which authorization mode your api request? (oauth or other) Have you used advanced operations?(merge, append, combine, refresh query, custom functions) Did refresh operation works on query editor side?

 

Please share more detail informations to help us clarify your scenario.

 

BTW, you can also refer to below link to know how to trace detail error message:

Handling 404–Not Found Errors With Web.Contents() In Power Query And Power BI

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
seann Frequent Visitor
Frequent Visitor

Re: 404 error when loading data to model

Thanks for your reply @v-shex-msft

 

I've traced the error to one of URL encoding. When loading the data to the model, Power BI is decoding a "%2f" in the URL to a "/", which must be encoded. See below.

 

https://[DOMAIN]/[PATH]%2fuser?[QUERY]

 

I've tried forcing the "/" to be encoded in the M query, which works in the Query Editor using "Uri.EscapeDataString("/").

let
    Encoded = Uri.EscapeDataString("/"),
    Source = Json.Document(Web.Contents("https://[DOMAIN]/[PATH]" & Encoded & "user?[QUERY]", [Headers=[Authorization="[AUTHKEY]"]]))
in
    Source

This is ignored when loading data to model and I'm received (redacted) the "/" decoded, which is causing the 404 Error.

 

OLE DB or ODBC error: [DataSource.Error] Web.Contents failed to get contents from 'https://[DOMAIN]/[PATH]/user?[QUERY]' (404): Not Found.

 

Unfortunately, the API I'm working with requires this section of the URL to be encoded (I've tested this outside of PBI). I'm able to view and model the data unimpeded in the query editor, but the encoding during the model loading is causing an Error 404. Is there a workaround for this?

 

Thanks for your help!

Community Support Team
Community Support Team

Re: 404 error when loading data to model

HI @seann,

 

Unfortunately, I also not found effective solutions to work through this issue.  Maybe you can take a look at following blog about handling special characters.

Handling special characters from html files with Power BI (M language).

 

@ImkeF Any suggestion with  web connector and character encode?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Super User
Super User

Re: 404 error when loading data to model

Sorry, this looks like a bug to me: You clearly have the correct strings in your URL and it loads correctly in the editor and when transforming to the model it does an extra conversion. You should file a bug-report for it. 

 

Theoretically one could enforce the string-evaluation by using "Expression.Evaluate", but unfortunately this doesn't work when loading to the model as well (but this is by design).

 

So the only workaround I can think of at the moment is to run the query in the editor and use an R- or Python-script to export from the editor to a csv-file and re-import from there.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: 404 error when loading data to model

Actually, another idea:

Try the "Relative Path"-method: https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

 

With any luck, this bypasses the problem:

 

let
    Encoded = Uri.EscapeDataString("/"),
    Source = Json.Document(Web.Contents("https://[DOMAIN]/[PATH]", 
RelativePath="user?[QUERY]",
Query=[Headers=[Authorization="[AUTHKEY]"]])) in Source

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




seann Frequent Visitor
Frequent Visitor

Re: 404 error when loading data to model

Thanks for the suggestions @v-shex-msft, but I'm having no issues dealing with special characters in the query editor, only when loading to model.

Highlighted
seann Frequent Visitor
Frequent Visitor

Re: 404 error when loading data to model

Thanks for the workaround @ImkeF. Unfortunately the "RelativePath" function seems to be a placeholder for "/", rather than the encoded "%2f" so I'm encountering the same 404 error. I'll file a bug report.

 

Can an R/Python script access the data in the query editor without it being loaded to model?

Super User
Super User

Re: 404 error when loading data to model

Yes, that's why I proposed it as a workaround 😉

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

seann Frequent Visitor
Frequent Visitor

Re: 404 error when loading data to model

Dropping the R code here for anyone who's facing the same dramas and has limited R skills like myself - credit to @ImkeF and http://www.instantr.com/2012/12/11/exporting-a-dataset-from-r/

 #"PREVIOUS STEP" = Whatever the last step was in M
#"Run R script" = R.Execute("write.csv(dataset, ""C:/Folder/File.csv"", row.names=F)",[dataset=#"PREVIOUS STEP"])
in
#"Run R script"

 

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,767)