Power query refresh times out intermittently when pulling data from Sharepoint and Google Sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-1925161%22%20slang%3D%22en-US%22%3EPower%20query%20refresh%20times%20out%20intermittently%20when%20pulling%20data%20from%20Sharepoint%20and%20Google%20Sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1925161%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EAbout%201%20out%20of%205%20times%2C%20Power%20Query%20in%20Excel%20will%20get%20hung%20up%20when%20pulling%20data%20from%20a%20.CSV%20or%20.TXT%20data%20file%20stored%20on%20our%20Sharepoint%20site.(%20I%20also%20have%20this%20problem%20at%20times%20when%20pulling%20data%20from%20Google%20Sheets%20into%20Excel%20via%20Power%20Query.)%26nbsp%3B%20I%20have%20checked%20all%20the%20data%20source%20credentials%20in%20Excel%20and%20everything%20looks%20fine.%20In%20the%20lower%20right%20had%20corner%20of%20Excel%2C%20it%20will%20simply%20say%20%22Connecting%20to%20datasource...%22%20and%20then%20nothing%20happens.%20After%205%20or%2010%20minutes%20or%20so%2C%20I%20can%20hit%20ESC%20to%20end%20the%20process.%20I%20am%20trying%20to%20run%20this%20process%20within%20an%20Excel%20Macro.%20The%20fact%20that%20it%20runs%20some%20of%20the%20time%2C%20but%20not%20all%20of%20the%20time%20leads%20me%20to%20believe%20that%20perhaps%20the%20issue%20is%20with%20Sharepoint%20itself.%26nbsp%3B%20Hence%2C%20my%20posting%20to%20this%20forum.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EIf%20I%20run%20the%20Power%20Query%20from%20the%20VBA%20Macro%2C%20it%20will%20throw%20a%20VBA%20error%20whenever%20it%20gets%20stuck%20when%20trying%20to%20pull%20data%20from%20the%20Sharepoint%20folder.%26nbsp%3B%20Again%2C%20this%20only%20happens%20about%201%20out%20of%205%20times%20the%20Macro%20is%20run.%26nbsp%3B%20%26nbsp%3BHere%20is%20some%20of%20the%20M%20Code%20(%20I%20replaced%20our%20company%20site%20name%20with%20XXXXXXX%20in%20the%20sample%20code%20below)%20for%20the%20Power%20Query%20that%20gets%20stuck%2C%20which%20is%20being%20run%20from%20a%20VBA%20Macro%20in%20Excel%3A%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSTRONG%3ESource%20%3D%20SharePoint.Files(%22%3CA%20href%3D%22https%3A%2F%2Fxxxxxxx.sharepoint.com%2Fsites%2FDataWarehouse%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20ugc%20noopener%20noreferrer%22%3Ehttps%3A%2F%2FXXXXXXX.sharepoint.com%2Fsites%2FDataWarehouse%3C%2FA%3E%22%2C%20%5BApiVersion%20%3D%2015%5D)%2C%3CBR%20%2F%3E%23%22Filtered%20Rows1%22%20%3D%20Table.SelectRows(Source%2C%20each%20(%5BFolder%20Path%5D%20%3D%20%22%3CA%20href%3D%22https%3A%2F%2FXXXXXXX..com%2Fsites%2FDataWarehouse%2FShared%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2FXXXXXXX..com%2Fsites%2FDataWarehouse%2FShared%3C%2FA%3E%20Documents%2FACT%2FContacts%2FSharepoint%20Reference%2F%22))%2C%3CBR%20%2F%3E%23%22Events%20txt_%3CA%20href%3D%22https%3A%2F%2Fxxxxxxx%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20ugc%20noopener%20noreferrer%22%3Ehttps%3A%2F%2FXXXXXXX%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Esharepoint%20com%2Fsites%2FDataWarehouse%2FShared%20Documents%2FACT%2FContacts%2FSharepoint%20Reference%2F%22%20%3D%20%23%22Filtered%20Rows1%22%7B%5BName%3D%22Charity%20Events.txt%22%2C%23%22Folder%20Path%22%3D%22%5BURL%5D%3CA%20href%3D%22https%3A%2F%2FXXXXXXX.sharepoint.com%2Fsites%2FDataWarehouse%2FShared%5B%2FURL%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2FXXXXXXX.sharepoint.com%2Fsites%2FDataWarehouse%2FShared%5B%2FURL%3C%2FA%3E%5D%20Documents%2FACT%2FContacts%2FSharepoint%20Reference%2F%22%5D%7D%5BContent%5D%2C%3CBR%20%2F%3E%23%22Imported%20CSV%22%20%3D%20Csv.Document(%23%22Events%20txt_%3CA%20href%3D%22https%3A%2F%2Fxxxxxxx%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20ugc%20noopener%20noreferrer%22%3Ehttps%3A%2F%2FXXXXXXX%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Esharepoint%20com%2Fsites%2FDataWarehouse%2FShared%20Documents%2FACT%2FContacts%2FSharepoint%20Reference%2F%22%2C%5BDelimiter%3D%22%2C%22%2C%20Columns%3D1002%2C%20Encoding%3D65001%2C%20QuoteStyle%3DQuoteStyle.None%5D)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EAny%20ideas%20why%20Power%20Query%20gets%20hung%20up%20Intermittently%20when%20pulling%20data%20from%20Sharepoint%3F%20Also%2C%20is%20there%20some%20code%20I%20could%20use%20in%20my%20VBA%20Macro%20that%20would%20tell%20it%20to%20skip%20the%20Power%20Query%20refresh%20step%20and%20move%20to%20the%20next%20step%20if%20it%20gets%20hung%20up%3F%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EThanks%20in%20advance%20for%20any%20help%20you%20can%20provide!%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EMatt%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1925161%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20%26amp%3B%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20Query%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

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 also have this problem at times when pulling data from Google Sheets into Excel via Power Query.)  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.  Hence, my posting to this forum.

If I run the Power Query from the VBA Macro, it will throw a VBA error whenever it gets stuck when trying to pull data from the Sharepoint folder.  Again, this only happens about 1 out of 5 times the Macro is run.   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, which is being run from a VBA Macro in Excel:


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

0 Replies