Using RelativePath in base URL for scheduled refresh

[zbeg]

Hello Power BI Community,

I am trying to combine the tables from each of the following with different domain names but similar data:

www.a.com/foo/table/

www.b.com/foot/table/

www.c.com/foo/table/

I know scheduled refresh does not work with dynamic URLs, so using a parameter:

Source = Web.BrowserContents("https://www." & DomainNameParameter & ".com/foo/table/")

will not allow me to do a scheduled refresh.

I've seen good solutions to this when modifying anything after the base URL with RelativePath as explained in this post by Chris Webb. But the RelativePath workaround only works after the base URL.

So something like:

Source = Web.BrowserContents("https://www/.",
    [RelativePath = DomainName & ".com/foo/table/"])

doesn't work, correct? 

Is there some approach I can use in order to use a dynamic URL in the base URL, or am I stuck having to refresh this dataset manually? 😞

Any help you can provide would be very much appreciated. Thank you!

[v-kelly-msft]

Hi @zbeg ，

Maybe API will help.

Check the thread below;

https://community.powerbi.com/t5/Service/Refresh-Dataset-with-Dynamic-Web-Query-with-API-Key-and-JW-...

Best Regards,
Kelly

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

[TomMartens]

Hey @zbeg ,

I'm wondering why you want to use a dynamic source?

I consider a domain a.com being a database and b.com a second database.

For this reason, I recommend to create at least 3 queries, each connecting to a separate database/domain and retrieve the source data. Then create a 4th query, and append/or merge the tables if needed to forrm your final data model if needed.

Hopefully, this provides what you are looking for.

Regards,

Tom

[zbeg]

What I'm really trying to do is combine the rosters, depth charts, and injury reports for all 32 NFL teams. So the URLs I want to combine into one table are:

www.azcardinals.com/team/injury-report

www.atlantafalcons.com/team/injury-report

www.chicagobears.com/team/injury-report

(29 more URLs)

Then do the same for

www.azcardinals.com/team/players-roster -- there are five tables I want to get from each URL

(31 more similar URLs)

And finally

www.azcardinals.com/team/depth-chart

(31 more similar URLs)

Then with all these loaded, I can build relationships -- this player is on X roster and is Y place on the depth chart and was injured but practiced Wednesday, etc.

But if I made each query its own query, I would have 224 different queries! That's clearly not sustainable. I have successfully gotten it down to a handful of queries using parameters, but I can't do a scheduled refresh.

I'm stuck and not sure how to even approach this problem architecturally.