Forum Discussion

kroman's avatar
kroman
Helper II
1 year ago

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

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:...
Need Help
lbendlin's avatar
lbendlin
Super User
1 year ago

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's avatar
kroman
Helper II
1 year ago

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"

    • kroman's avatar
      kroman
      Helper II
      1 year ago

       

       

      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's avatar
        lbendlin
        Super User
        1 year ago

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