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