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

[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.