So I have an unusual use case. I have created a dataset that goes out to each of the 32 NFL teams and downloads:
Their roster (5 tables)
Their injury report (1 table)
Their depth chart (1 table)
https://www.azcardinals.com/team/player-roster
https://www.atlantafalcons.com/team/player-roster
https://www.chicagobears.com/team/player-roster
(29 more similar URLs)
https://www.azcardinals.com/team/injury-report
https://www.atlantafalcons.com/team/injury-report
(30 more)
https://www.azcardinals.com/team/depth-chart
https://www.atlantafalcons.com/team/depth-chart
(30 more)
This way I can look at each team's website, who is on the active roster, who is listed under COVID-reserve, injured reserve, etc., if someone missed practice I can relate that to the depth chart and see if the starter is injured. It's very neat. This is a simplified query with just three teams (but yields the same problem) so you can follow along if you'd like:
let
TeamList = {
"azcardinals", "atlantafalcons", "chicagobears"
},
Get_DepthChart = (teamName as text) =>
/* extracts table from: www.foo.com/team/depth-chart */
let
Source = Web.BrowserContents("https://www." & teamName & ".com/team/depth-chart"),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "DIV[id='scroll-offense'] > DIV.d3-o-table--horizontal-scroll > TABLE.d3-o-table.d3-o-table--row-striping.d3-o-table--detailed.d3-o-depthchart > * > TR > :nth-child(1)"}, {"Column2", "DIV[id='scroll-offense'] > DIV.d3-o-table--horizontal-scroll > TABLE.d3-o-table.d3-o-table--row-striping.d3-o-table--detailed.d3-o-depthchart > * > TR > :nth-child(2)"}, {"Column3", "DIV[id='scroll-offense'] > DIV.d3-o-table--horizontal-scroll > TABLE.d3-o-table.d3-o-table--row-striping.d3-o-table--detailed.d3-o-depthchart > * > TR > :nth-child(3)"}, {"Column4", "DIV[id='scroll-offense'] > DIV.d3-o-table--horizontal-scroll > TABLE.d3-o-table.d3-o-table--row-striping.d3-o-table--detailed.d3-o-depthchart > * > TR > :nth-child(4)"}, {"Column5", "DIV[id='scroll-offense'] > DIV.d3-o-table--horizontal-scroll > TABLE.d3-o-table.d3-o-table--row-striping.d3-o-table--detailed.d3-o-depthchart > * > TR > :nth-child(5)"}}, [RowSelector="DIV[id='scroll-offense'] > DIV.d3-o-table--horizontal-scroll > TABLE.d3-o-table.d3-o-table--row-striping.d3-o-table--detailed.d3-o-depthchart > * > TR"]),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}})
in
#"Changed Type",
List_DepthChartTables = List.Transform(
TeamList,
each Get_DepthChart(_)
),
#"Converted to Table" = Table.FromList(List_DepthChartTables, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"Column1", "Column2", "Column3", "Column4", "Column5"}, {"Column1.Column1", "Column1.Column2", "Column1.Column3", "Column1.Column4", "Column1.Column5"})
in
#"Expanded Column1"
However, when I try to set up a scheduled refresh in the Power BI Service, I get the following error:
"This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources."
Okay, so let's wander over to the URL and see what it says (emphasis mine):
"A dynamic data source is a data source in which some or all of the information required to connect cannot be determined until Power Query runs its query, because the data is generated in code or returned from another data source. Examples include: the instance name and database of a SQL Server database; the path of a CSV file; or the URL of a web service.
In most cases, Power BI datasets that use dynamic data sources cannot be refreshed in the Power BI service. There are a few exceptions in which dynamic data sources can be refreshed in the Power BI service, such as when using the RelativePath and Query options with the Web.Contents M function. Queries that reference Power Query parameters can also be refreshed."
Indeed, doing some Googling Binging shows that the RelativePath and Query options are a workaround to this error.
https://medium.com/datadriveninvestor/setting-a-scheduled-refresh-on-a-dynamic-data-source-in-power-...
https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...
https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...
As Chris Webb writes (emphasis once again mine):
"The problem is that when a published dataset is refreshed, Power BI does some static analysis on the code to determine what the data sources for the dataset are and whether the supplied credentials are correct. Unfortunately in some cases, such as when the definition of a data source depends on the parameters from a custom M function, that static analysis fails and therefore the dataset does not refresh."
However, each of these solutions are with the same base url. www.domain.com/q=24601, www.domain.com/q=31415 etc.
Here it's www.domain1.com/blah www.domain2.com/blah, which doesn't work with RelativePath in Web.Contents().
Excerpted from the WebContents() documentation:
"The record can contain the following fields:
RelativePath: Specifying this value as text appends it to the base URL before making the request."
So my RelativePath savior doesn't work because it only appends to the end of the base URL. Phooey.
Loading each table individually is not practical, as there are 224 of them!
I'm completely stuck and don't know where to go from here. Is this a Power Automate situation? (I've never used Power Automate before, but I can go with the flow if that's my only good option, but I would really like to stay in Power BI if I can.)
