MGTexas123
New Member
- Joined
- Jan 1, 2020
- Messages
- 9
- Office Version
- 365
- Platform
- 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
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