Dynamically query all SharePoint sites with same list name, but can't refresh online

[mroberts_troy]

I have built a query that searches SharePoint for a list of all site URLs, and allows me to query all the sites which contain an SP List that are all named the same.  However, whenever I publish, I get the error that because this contains a dynamic query, I can't refresh it online.  Can anyone please assist with figuring out a different way to handle this?  Here's my code:

let
    Source = OData.Feed("https://EXAMPLESITE.sharepoint.com/_api/search/query?querytext='contentclass:STS_Site contentclass:STS_Web'&selectprooperties='Title,Path'"),
    PrimaryQueryResult = Source[PrimaryQueryResult],
    RelevantResults = PrimaryQueryResult[RelevantResults],
    Table = RelevantResults[Table],
    Rows = Table[Rows],
    #"Converted to Table" = Table.FromList(Rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Cells"}, {"Cells"}),
    #"Expanded Cells" = Table.ExpandListColumn(#"Expanded Column1", "Cells"),
    #"Expanded Cells1" = Table.ExpandRecordColumn(#"Expanded Cells", "Cells", {"Key", "Value", "ValueType"}, {"Key", "Value", "ValueType"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Cells1", each [Key] = "Path" or [Key] = "Title"),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.Contains([Value], "22")),
    #"Added Index1" = Table.AddIndexColumn(#"Filtered Rows1", "Index", 0, 1, Int64.Type),
    #"Added Conditional Column" = Table.AddColumn(#"Added Index1", "Row ID", each if [Key] = "Title" then [Index] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Row ID"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"}),
    #"Pivoted Column1" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Key]), "Key", "Value"),
    #"Renamed Columns2" = Table.RenameColumns(#"Pivoted Column1",{{"Path", "Value"}, {"ValueType", "Key"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns2",{"Row ID"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Title", "Project Title"}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Key", type text}, {"Value", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type3", "ListData", each SharePoint.Tables([Value], [Implementation=null, ApiVersion=15])),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom1", "SiteIndex", 1, 1, Int64.Type),
    #"Expanded ListData" = Table.ExpandTableColumn(#"Added Index", "ListData", {"Id", "Title", "Items", "Content", "Name", "Folder Path"}, {"Id", "Title", "Items", "Content", "Name", "Folder Path"}),
    #"Filtered Rows3" = Table.SelectRows(#"Expanded ListData", each ([Title] = "Deliverables")),
    #"Expanded Items" = Table.ExpandTableColumn(#"Filtered Rows3", "Items", {"ID", "Title", "Modified", "Created", "AuthorId", "PercentComplete", "DraftDueDate", "Draft Complete Date", "FinalDueDate", "FinalCompleteDate", "Organization", "FinalPercentComplete", "Comments", "FieldValuesAsText"}, {"ID.1", "Title.1", "Modified", "Created", "AuthorId", "PercentComplete", "DraftDueDate", "Draft Complete Date", "FinalDueDate", "FinalCompleteDate", "Organization", "FinalPercentComplete", "Comments", "FieldValuesAsText"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Items",{{"ID.1", type text}, {"SiteIndex", type text}}),
    #"Expanded FieldValuesAsText" = Table.ExpandRecordColumn(#"Changed Type2", "FieldValuesAsText", {"FileRef", "AssignedTo"}, {"FileRef", "AssignedTo"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded FieldValuesAsText",{"Project Title", "Value", "Title", "ID.1", "Title.1", "Modified", "Created", "PercentComplete", "DraftDueDate", "Draft Complete Date", "FinalDueDate", "FinalCompleteDate", "Organization", "FinalPercentComplete", "Comments", "AssignedTo", "SiteIndex"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1",{{"Value", "Site"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Site", type text}, {"Title", type text}, {"Modified", type datetime}, {"Created", type datetime}, {"PercentComplete", Int64.Type}, {"DraftDueDate", type datetime}, {"Draft Complete Date", type datetime}, {"FinalDueDate", type datetime}, {"FinalCompleteDate", type datetime}, {"Organization", type any}, {"FinalPercentComplete", type any}, {"Comments", type any}, {"AssignedTo", type text} }),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Changed Type", {{"Site", each Text.AfterDelimiter(_, "/", {0, RelativePosition.FromEnd}), type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Extracted Text After Delimiter","",null,Replacer.ReplaceValue,{"AssignedTo"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Replaced Value", {{"SiteIndex", type text}, {"ID.1", type text}}, "en-US"),{"SiteIndex", "ID.1"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"DistinctTasks"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Site", type text}, {"Title", type text}, {"DistinctTasks", type text}, {"Title.1", type text}, {"Modified", type date}, {"Created", type date}, {"PercentComplete", Percentage.Type}, {"DraftDueDate", type date}, {"Draft Complete Date", type date}, {"FinalDueDate", type date}, {"FinalCompleteDate", type date}, {"Organization", type any}, {"FinalPercentComplete", type any}, {"Comments", type any}, {"AssignedTo", type text}}),
    #"Filtered Rows2" = Table.SelectRows(#"Changed Type1", each [Title.1] <> null and [Title.1] <> ""),
    #"Replaced Value1" = Table.ReplaceValue(#"Filtered Rows2"," - Confidential","",Replacer.ReplaceText,{"Project Title"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "Project Title", Splitter.SplitTextByDelimiter(" - ", QuoteStyle.Csv), {"Project Number", "Client", "Project Title"}),
    #"Added Conditional Column1" = Table.AddColumn(#"Split Column by Delimiter", "OrgText", each if Value.Is([Organization], type text) then [Organization] else null, type text),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "OrgList", each if Value.Is([Organization], type list) then [Organization] else null),
    #"Expanded OrgList" = Table.ExpandListColumn(#"Added Conditional Column2", "OrgList"),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded OrgList",{"Organization"}),
    #"Merged Columns1" = Table.CombineColumns(#"Removed Columns2",{"OrgText", "OrgList"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Organization"),
    #"Filtered Rows4" = Table.SelectRows(#"Merged Columns1", each ([Organization] = "EXAMPLE"))
in
    #"Filtered Rows4"

[DaxNovice]

I have a similar issue, 

A list of sharepoint sites stored in a list.  I just wat to know how many files have been submitted to each site each month

Works fine on pbix, but too dynamic for dataflow or service refresh.  

This should be possible, its within Microsoft's infrastructure and surely not that unusual

Custom function to count all files by month

let
    Source = (SP as text ) =>

let
    Source = SharePoint.Files(SP),
   
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "/Sharing/")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date created", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date created"}, {{"Files by Day", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"
in
    Source

[v-zhangti]

Hi, @mroberts_troy 

You can refer to the following link, hope it can help you.

https://community.powerbi.com/t5/Desktop/Cannot-do-schedule-refresh-for-data-connect-to-SharePoint-l... 

https://community.powerbi.com/t5/Power-Query/Assistance-with-power-Query-nd-Sharepoint-online-list/m... 

https://community.powerbi.com/t5/Power-Query/Power-Query-in-Sharepoint/m-p/2286605 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mroberts_troy]

Unfortunately, none of these are related to my effort.  I have tried following Chris Webb's tutorials, but I get lost in the code and so far still haven't found anything that works.  Someone did ask about getting version history from lists, but all their lists were in one site, so that was a no-go.  Still looking.