Getting 'One or more table references a dynamic Data Source' even with Web.Contents with Relativepat

[kroman]

Hi All

I have a simple query where trying to get a site url from one item in SP list and then get files list from that site, by following code:

let
    Source0 = Json.Document(Web.Contents("https://MyOrg.sharepoint.com/sites/DigitalWorkplaceReporting/_api",
      [  
        RelativePath="web/lists/GetByTitle('SPSites8')/items",
        Query=[
          #"$top"="50", //This is the size of the batch
          #"$filter"="StatusN eq 1"
           ],
        Headers=[  Accept="application/json;odata=nometadata"   ]  //changing headers return a JSON Document
              
       ] 
     )
     ),
    value = Source0[value],
    value1 = value{0},
    TheSite = value1[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"

but still getting 'One or more table references a dynamic Data Source' despite using Web.Contents with Relativepath option (which, lots of people claims, should resolve this issue)
Any idea why its happening? 

Thanks

[lbendlin]

No need to use Web.Contents for SharePoint lists.  Use the Standard SharePoint List connector.

[kroman]

And how is that supose to sort the problem?

[lbendlin]

You will have a static Site URL  that will satisfy the "dynamic data source monster".  All the dynamic stuff can then happen as if you were using RelativePath.

[kroman]

Its giving the same error, I started with trying to do it with Standard SharePoint List connector and thats why I tried to do it with Web.Contents

[lbendlin]

In the code you posted "Source0"  is not referenced later on, so it won't be executed anyway.

Please avoid sorting tables in Power Query if possible - it is a very expensive operation that breaks spooling.

Can you please describe what kind of business problem you are attempting to solve?

[kroman]

You realy need to learn to pay attention 🙂
Source0 referenced in line 14 - 

value = Source0[value],

Sorting table is nothing to do with a problem
Problem clearly described in a post subject and description - "Getting 'One or more table references a dynamic Data Source' even with Web.Contents with Relativepat"

[lbendlin]

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

[kroman]

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"

[lbendlin]

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

[kroman]

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

[lbendlin]

Understand. What if you have a static list of sites?