Forum Discussion
Refresh with a column containing a custom function
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
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.
5 Replies
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!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.
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.
Hi Darryl_Rosin,
Checking in to see if your issue has been resolved. let us know if you still need any assistance.
Thank you.
