PLEASE HELP ME! Virtual Data Gateways help

[jmillsjmills]

I have successfully built a report in Power BI and published it to my workspace.

The initial data source is a static URL (https://peelhunt.my.salesforce-sites.com/SingletrackCMS__DownloadDocument?

uid=3713153d-15f6-488c-912...) but this static URL then auto-directs to a URL that changes each day.

In Power BI Desktop I am therefore using Lines.FromBinary(Web.Contents(... around the static URL to collect the source code and then find the dynamic URL each day. I can then pull in the data that I need from that dynamic URL easily enough.

For example, today the daily URL is:

https://peelhunt.s3.amazonaws.com/Peel Hunt Investment Companies Data Sheet_2023-10-24.xlsx?X-Amz-Al...

In order to not have a "Dynamic Data Source" (an initial error I was getting where the whole URL was a variable), I've used the RelativePath method when building the code as follows: 

Custom1 = Excel.Workbook(Web.Contents("https://peelhunt.s3.amazonaws.com",[RelativePath="" & symbol &""]), null, true)

where of course "symbol" is a parameter containing the rest of the string ... i.e. "Peel Hunt Investment Companies Data Sheet_2023-10-24.xlsx?X-Amz-Algorithm=AWS4-..........." etc

This method works perfectly in Power BI Desktop version - refreshes exactly as I want daily.

BUT ! When it comes to setting the published dataset up for scheduled refresh in the cloud, I need to map new credentials to the dataset via a Virtual Network Data Gateway. For the three other sources in the dataset (including the static URL), this is configured fine. BUT this "https://peelhunt.s3.amazonaws.com" domain is the tricky one.

Clearly I need ultimately the authentication elements that sit in "RelativePath" within the query for a successful connection in the first place (the RelativePath will still change daily, but the Power Query successfully collects the new tokens etc). In my Desktop version, it connects fine thanks to this. But establishing a new credential on the cloud, I fail to connect to the direct domain without the RelativePath string (even if I tick "Skip Test Connection"):

And although I could set up the credential successfully for the full URL beyond https://peelhunt.s3.amazonaws.com, clearly this is what will change each day... Having a credential set up directly to the full URL doesn't help me (I think). 

So I thought it could be that I need to just do the "https://peelhunt.s3.amazonaws.com" domain, select "OAuth 2.0" and I could enter the various Headers and Keys encoded within the URL in order to get a successful connection. However doing this, I get this error instead:

I would REALLY appreciate some help here please as I'm all out of ideas. I realise this is a slightly heavy duty one.. Not as fun as playing around with the data itself. But I hope I'm posting this in the right place to find someone who can help me!!

[Divyaraj_Rathod]

This is a known pain point with Web.Contents + RelativePath data sources when moving from Desktop to the Service/Gateway. A couple of things worth checking:

1. Exact URL match: the "data source" that Power BI registers for credential mapping is exactly the first argument you pass to Web.Contents, i.e. "https://peelhunt.s3.amazonaws.com" - no trailing slash, no path. Whatever you enter in the gateway's "New connection" URL field for that data source has to match this character-for-character (protocol, case, trailing slash). Even one extra "/" will cause the credential you create not to bind to the source your query actually uses, which shows up as this kind of generic "Invalid connection credentials" failure.

2. Anonymous + S3 bucket root: since https://peelhunt.s3.amazonaws.com on its own (no object key) typically returns a 403 AccessDenied from S3, some versions of the connection-test dialog treat that non-2xx response as a credential failure even with "Skip Test Connection" ticked - this checkbox isn't consistently honored for Web/Anonymous sources in every entry point of the UI.

Workaround that tends to work: instead of adding/editing this data source credential from the inline prompt on the dataset's "Data source credentials" settings page, go to Manage gateways (admin portal) > your VNet/On-prem gateway > Data source connections, and add the Web data source directly there with Authentication = Anonymous and Skip Test Connection checked. That admin-side flow tends to actually respect the skip flag for Web sources, whereas the dataset-settings "New connection" tile forces a live test regardless.

If it still fails at that point, it's worth confirming the Privacy Level set for this source is consistent (e.g. Public/None for all four sources in the dataset) - a privacy level mismatch across sources in the same dataset also surfaces as a generic "Unable to create connection" error at publish/refresh time.