Web.Contents - Sites that do not have an "accessible" endpoint.

[Anonymous]

Hi all,

I've been having luck using the previously mentioned RelativePath to workaround the typical Scheduled Refresh issue encountered when trying to refresh against websites. See here for info Web.Contents(), M Functions And Dataset Refresh Errors In Power BI

Where I have had some issues are where the underlying site does not provide a successful 200 response when visiting the base URL.  Typically throwing up a 404 or 403 response and failing the PowerBI service Data source credentials "test".

Has anyone found a way around this kind of situation?  Some Power Query M addition that says "Ignore response" or something.

For (some) clarity, here is an abridged version of my query: -

let
	TokenBaseURL            = "https://login.microsoftonline.com",
	TokenRelativePath       = "{DirectoryGUID}/oauth2/token",
	TokenContent            = "grant_type=client_credentials&client_id={ClientID}&redirect_uri=https%3A%2F%2Fapi.loganalytics.io&resource=https%3A%2F%2Fapi.loganalytics.io%2F&client_secret={ClientSecret}",
	BaseURL			= "https://api.loganalytics.io",
	RelativePath		= "v1/workspaces/{workspaceID}/query",

    	GetToken =
        	let Options 		= [Content=Text.ToBinary(TokenContent), Headers=[#"Content-Type"="application/x-www-form-urlencoded"], RelativePath=TokenRelativePath],
            		RawData 	= Web.Contents(TokenBaseURL, Options),
            		Document 	= Json.Document(RawData),
            		Token 		= Document[access_token]
        	in Token,

    	GetResults = 
		let Query		= "{""query"": ""CustomLogs|{somequery}""}",
        		Options		= [Content=Text.ToBinary(Query), Headers=[Authorization="Bearer "&GetToken,#"Content-Type"="application/json"], RelativePath=RelativePath],
        		RawData		= Web.Contents(BaseURL, Options),
        		Document	= Json.Document(RawData)
        	in Document

in
	GetResults 

When attempting to add this to app.powerbi.com, I can successfully use login.microsoftonline.com (as it returns a 200 response) but cannot do the same for api.loganalytics.io.

Result: -

Failed to update data source credentials: Web.Contents failed to get contents from 'https://api.loganalytics.io/' (404): Not Found

[TeigeGao]

Hi @Anonymous ,

Please refer to this document: https://dev.loganalytics.io/, we will need to obtain an authentication token to get data from this site.

Best Regards,

Teige

[Anonymous]

Hi @TeigeGao ,

I am already using the authentication token as per the GetToken function.

This all works fine in Desktop, the problem is when moving it to the online service.

[Anonymous]

So, I stumbled across an article which helped point me at something that appeared to be of help.

https://prathy.com/2017/09/calling-power-bi-api-using-power-bi-desktop-to-document-power-bi-service/

https://blog.crossjoin.co.uk/2016/08/09/handling-404-not-found-errors-with-web-contents-in-power-que...

Specifically this: - https://docs.microsoft.com/en-us/power-query/waitretry (ManualStatusHandling)

However, even with this added, PowerBI Service refuses to accept the data source.

[Anonymous · Accepted solution]

Hopefully, this recent feature will give me the workaround I need.

Skip Test Connection for On-premises and Cloud Data Sources