Refresh with a column containing a custom function

[Darryl_Rosin]

Hello.

My semantic model reads from a sharepoint list. Each row has a column that contains a unique url to another sharepoint site. I have a function that takes the site url from this column and reads data from a sharepoint list on the site. This is producing the 'dynamic data source error' when I try to refresh the model in the powerBI service. How can I refactor things so I can do this? It seems like I should be able to loop through a list of sites and collect data, but the obvious way is the numbskull way?

Thnaks!

Main Query
let
  Source = SharePoint.Tables("https://tenancy.sharepoint.com/sites/Hub", [Implementation = "2.0", ViewMode = "Default"]),
  #"Navigation 1" = Source{[Id = "87f4f53F-dec1-44c9-ae84-53bc68af306e"]}[Items],
  #"Removed Other Columns" = Table.SelectColumns(#"Navigation 1",{"DataUrl"}),
  #"Invoked Custom Function" = Table.AddColumn(#"Removed Other Columns", "Indicator Column", each    #"Get Indicator"([DataUrl])),
  #"Removed Errors" = Table.RemoveRowsWithErrors(#"Invoked Custom Function", {"Indicator Column"}),
  #"Expanded Indicator Column" = Table.ExpandTableColumn(#"Removed Errors", "Indicator Column", {"Modified", "Overall"}, {"Modified", "Overall"})
in
  #"Expanded Indicator Column"

"Get Indicator" Function
let
  Source = (SiteURL as text) => let
    Source = SharePoint.Tables(SiteURL, [Implementation=null, ApiVersion=15]),
    #"580ca18d-db85-41d6-b64e-0d6fcf455b71" = Source{[Title="Status Update"]}[Items],
    #"Sorted Rows" = Table.Sort(#"580ca18d-db85-41d6-b64e-0d6fcf455b71",{{"Id", Order.Ascending}}),
    #"Kept Last Rows" = Table.LastN(#"Sorted Rows", 1),
    #"Removed Other Columns" = Table.SelectColumns(#"Kept Last Rows",{"Modified", "Overall Health"})
  in
    #"Removed Other Columns"
in
  Source

[v-saisrao-msft]

Hi @Darryl_Rosin,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

[grazitti_sapna]

Hi @Darryl_Rosin,

Please try with below Query, The refactored query connects once to your SharePoint tenant using a fixed base URL, retrieves the available lists and site metadata, and then filters internally to only those sites that match the URLs from your original list. Instead of calling each site dynamically, it locates the relevant “Status Update” lists within that single connection, extracts the required fields like Modified and Overall Health, and returns a combined dataset (optionally keeping the latest record per site). By doing everything through one static connection, it avoids the dynamic data source issue and allows the dataset to refresh successfully in the Power BI Service.

let
// 🔹 Static root connection (VERY IMPORTANT)
Source = SharePoint.Tables("https://tenancy.sharepoint.com", [Implementation = "2.0"]),

// 🔹 Get your hub list (where DataUrl exists)
HubList = Source{[Id="87f4f53F-dec1-44c9-ae84-53bc68af306e"]}[Items],

// 🔹 Keep only URL column
KeepCols = Table.SelectColumns(HubList, {"DataUrl"}),

// 🔹 Extract site path (relative part after domain)
AddRelativePath = Table.AddColumn(
KeepCols,
"RelativePath",
each Text.AfterDelimiter([DataUrl], ".com"),
type text
),

// 🔹 Filter all SharePoint tables matching those sites
FilteredSites = Table.SelectRows(
Source,
(row) =>
List.AnyTrue(
List.Transform(
AddRelativePath[RelativePath],
(path) => Text.Contains(row[Url], path)
)
)
),

// 🔹 Filter only required list
FilteredLists = Table.SelectRows(
FilteredSites,
each [Title] = "Status Update"
),

// 🔹 Expand list items
Expanded = Table.ExpandTableColumn(
FilteredLists,
"Items",
{"Modified", "Overall Health"},
{"Modified", "Overall"}
),

// 🔹 Optional: Keep latest record per site
Sorted = Table.Sort(Expanded, {{"Modified", Order.Descending}}),

// 🔹 Remove duplicates (latest per site)
Final = Table.Distinct(Sorted, {"Url"})
in
Final

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[Darryl_Rosin]

Thank-you, but Source in your script returns a table with 20 rows. The list on /sites/Hub is not included so HubList returns an error.

[v-saisrao-msft]

Hi @Darryl_Rosin,

Have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further.

Thank you.

[v-saisrao-msft · Accepted solution]

Hi @Darryl_Rosin,

The issue occurs because your query builds the SharePoint URL dynamically from a column, creating a dynamic data source that Power BI Service cannot refresh. Microsoft documentation states that such data sources are not supported for refresh in most cases. Try using a static base URL and avoid passing full URLs dynamically; instead, use Web.Contents with RelativePath so only the path changes while the data source remains fixed. This allows the dataset to refresh successfully in the Service.

Web.Contents - PowerQuery M | Microsoft Learn

Data refresh in Power BI - Power BI | Microsoft Learn

Thank you.