Setting up Scheduled Refresh without Data Gateway for a folder on SharePoint

[danb]

Power BI Experts, 
I am running into a challenge here with a report. I am needing to connect to different folders (all on SharePoint) for our organization. I don't want to usea data gateway at this point. I tried going down the path of "Sharepoint.Content" that i found in this blog:
https://radacad.com/power-bi-get-data-from-multiple-files-in-a-folder-on-onedrive-for-business-no-ga...

Unfortunately because I am having to connect to 10 different folders I am getting time out errors. Is there an effecient work around for this? Here is my query for one of them.

I also have the code here if you want to copy and paste it:
let
Query1 = SharePoint.Contents("https://xxxxxxxxx.sharepoint.com/XXXXXXX"),
Documents = Query1{[Name="Documents"]}[Content],
Administration = Documents{[Name="Administration"]}[Content],
PowerBI = Administration{[Name="PowerBI"]}[Content],
Procurement = PowerBI{[Name="Procurement"]}[Content],
#"PBA Visualization Tool" = Procurement{[Name="PBA Visualization Tool"]}[Content],
Projects = #"PBA Visualization Tool"{[Name="Projects"]}[Content],
#"Removed Other Columns" = Table.SelectColumns(Projects,{"Content", "Name"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Custom.Kind] = "Sheet")),
#"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"Name", "Custom.Data", "Custom.Item"}),
#"Expanded Custom.Data" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom.Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34"}, {"Custom.Data.Column1", "Custom.Data.Column2", "Custom.Data.Column3", "Custom.Data.Column4", "Custom.Data.Column5", "Custom.Data.Column6", "Custom.Data.Column7", "Custom.Data.Column8", "Custom.Data.Column9", "Custom.Data.Column10", "Custom.Data.Column11", "Custom.Data.Column12", "Custom.Data.Column13", "Custom.Data.Column14", "Custom.Data.Column15", "Custom.Data.Column16", "Custom.Data.Column17", "Custom.Data.Column18", "Custom.Data.Column19", "Custom.Data.Column20", "Custom.Data.Column21", "Custom.Data.Column22", "Custom.Data.Column23", "Custom.Data.Column24", "Custom.Data.Column25", "Custom.Data.Column26", "Custom.Data.Column27", "Custom.Data.Column28", "Custom.Data.Column29", "Custom.Data.Column30", "Custom.Data.Column31", "Custom.Data.Column32", "Custom.Data.Column33", "Custom.Data.Column34"}),

Thanks for your help!

[danb]

@lbendlin - thanks for the reply. So would you recommend layering in the binary buffer code to each sub folder drill in step or just the one that you showed above? 

[lbendlin]

only in the step where you actually pull content.

[danb]

@lbendlin - thanks for the response. I tried that but clearly the issue is between the seat and the keyboard (on my end). I ended up throwing in the towel and setting up a personal data gateway on a VM to manage the refresh and that seems to be working as needed. I will continue to look into the Function.InvokeAfter() function and maybe will be able to get it figured out down the road. 

[lbendlin]

Try binary buffers.

For example 

#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each try Excel.Workbook([Content]) otherwise Binary.Buffer(Excel.Workbook([Content]))),

Also, learn about Function.InvokeAfter()