Refresh with a column containing a custom function

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!