Power query refresh times out intermittently when pulling data from Sharepoint

MGTexas123

New Member
Joined
Jan 1, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
About 1 out of 5 times, Power Query in Excel will get hung up when pulling data from a .CSV or .TXT data file stored on our Sharepoint site. I have checked all the data source credentials in Excel and everything looks fine. In the lower right had corner of Excel, it will simply say "Connecting to datasource..." and then nothing happens. After 5 or 10 minutes or so, I can hit ESC to end the process. I am trying to run this process within an Excel Macro. The fact that it runs some of the time, but not all of the time leads me to believe that perhaps the issue is with Sharepoint itself.

If I run the Power Query from the VBA Macro, it will the throw a VBA error whenever it gets stuck when trying to pull data from the Sharepoint folder. Here is some of the M Code ( I replaced our company site name with XXXXXXX in the sample code below) for the Power Query that gets stuck:


Source = SharePoint.Files("https://XXXXXXX.sharepoint.com/sites/DataWarehouse", [ApiVersion = 15]),
#"Filtered Rows1" = Table.SelectRows(Source, each ([Folder Path] = "https://XXXXXXX..com/sites/DataWarehouse/Shared Documents/ACT/Contacts/Sharepoint Reference/")),
#"Events txt_https://XXXXXXX sharepoint com/sites/DataWarehouse/Shared Documents/ACT/Contacts/Sharepoint Reference/" = #"Filtered Rows1"{[Name="Charity Events.txt",#"Folder Path"="[URL]https://XXXXXXX.sharepoint.com/sites/DataWarehouse/Shared[/URL] Documents/ACT/Contacts/Sharepoint Reference/"]}[Content],
#"Imported CSV" = Csv.Document(#"Events txt_https://XXXXXXX sharepoint com/sites/DataWarehouse/Shared Documents/ACT/Contacts/Sharepoint Reference/",[Delimiter=",", Columns=1002, Encoding=65001, QuoteStyle=QuoteStyle.None]),


Any ideas why Power Query gets hung up Intermittently when pulling data from Sharepoint? Also, is there some code I could use in my VBA Macro that would tell it to skip the Power Query refresh step and move to the next step if it gets hung up?

Thanks in advance for any help you can provide!

Matt
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Watch MrExcel Video

Forum statistics

Threads
1,126,932
Messages
5,621,682
Members
415,849
Latest member
PhoenixRising2015

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top