Forum Discussion
Getting 'One or more table references a dynamic Data Source' even with Web.Contents with Relativepat
You're correct, I missed that line.
Can you please post the refactored code that uses SharePoint.List instead of the API call?
Power Query SharePoint list connector - Power Query | Microsoft Learn
let
Source0 = SharePoint.Tables("https://MyOrg.sharepoint.com/sites/DigitalWorkplaceReporting", [Implementation="2.0", ViewMode="All"]),
#"ffdf74ed-79dd-4c01-a2f3-14bead82700f" = Source0{[Id="ffdf74ed-79dd-4c01-a2f3-14bead82700f"]}[Items],
#"Filtered Rows" = Table.SelectRows(#"ffdf74ed-79dd-4c01-a2f3-14bead82700f", each ([StatusN] = 1)),
TheSite = Table.First(#"Filtered Rows")[SiteUrl],
Source = SharePoint.Files(TheSite, [ApiVersion = 15]),
#"Sorted rows" = Table.Sort(Source, {{"Date created", Order.Ascending}}),
#"Filtered rows" = Table.SelectRows(#"Sorted rows", each [Date modified] < #datetime(2017, 5, 1, 0, 0, 0)),
#"Sorted rows 1" = Table.Sort(#"Filtered rows", {{"Date modified", Order.Ascending}}),
#"Expanded Attributes" = Table.ExpandRecordColumn(#"Sorted rows 1", "Attributes", {"Size"}, {"Size"}),
#"Changed column type" = Table.TransformColumnTypes(#"Expanded Attributes", {{"Size", type number}}),
#"OldFiles" = Table.Sort(#"Changed column type", {{"Size", Order.Descending}}),
#"Removed Columns" = Table.RemoveColumns(OldFiles,{"Content"})
in
#"Removed Columns"
I see. So you have a SharePoint list that contains a list of sharepoint sites. You filter the list by a certain criteria and are then attempting to list all the files of the first site in that list.
Source = SharePoint.Files(TheSite,That then triggers the dynamic source warning, and rightfully so.
How big is the list? I would try not to use the Table.First filter, at least not at that point). Maybe for each list item add the SharePoint.Files table object as a custom column, and only then say "Keep first row".
Nothing to do with filtering, I tried loading site url as single value from another website with
Source = SharePoint.Files(Text.FromBinary(Web.Contents("https://SomeSite.net/thelink.html" )) , [ApiVersion = 15]),but still getting same problem
Understand. What if you have a static list of sites?