Power Query opens files while importing from folder, then errors out

[LJRome]

Recently, when I've tried to refresh data from a folder of 157 files, Excel's been opening a few files from the source folder, and that stops the import. I'd close the files, refresh data again, and it runs fine. Seems to happen only on first refresh. Haven't read anything about this anywhere, could sure use your help. - Thanks; LJ

[LJRome]

Thanks Pete. The exports are in a SharePoint folder, and I use the From Folder connector that's built into Excel 2013's PQ add-in. After I posted this question it occurred to me that this might be a SharePoint/Windows problem, so instead of refreshing upon Check Out I now close all instances of Excel and File Explorer first, relaunch just the Excel file that contains the queries, then refresh data. Problem has not recurred since. It's a workaround, not a fix, works well enough for me, but might be good to see what's at the root of it. 

Here's the M Code to import the files (several other queries after that - Windows freezes when I put all queries into one, so I reference this one into the next query instead):

let
Source = Folder.Files("C:\Users\helpdesk\SharePoint\NonProfitHousing\Trackers\Delinquent & Prepaid\_ExportsCurrent"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from _ExportsCurrent", each #"Transform File from _ExportsCurrent"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from _ExportsCurrent"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from _ExportsCurrent", Table.ColumnNames(#"Transform File from _ExportsCurrent"(#"Sample File")))
in
#"Expanded Table Column1"

[BA_Pete · Accepted solution]

Hi @LJRome ,

The first thing I can see is that, even though you mention getting files from a SharePoint folder, PBI is actually using your C: drive as the source. Whether this is causing what you describe I can't be sure of, but I'd certainly look to change all your sources to the correct SharePoint web path.

You should use the SharePoint Folder connector to point to the folder with all your files in. It will generate a source step that looks something like this:

Source = SharePoint.Contents("https://XXXX.sharepoint.com/XXXX/XXXX/"),
folderName1= Source{[Name = "folderName1"]}[Content],
folderName2 = folderName1{[Name = "folderName2"]}[Content],
folderName3 = folderName2{[Name = "folderName3"]}[Content],
nextSteps = ...
...

The number of 'folderNameX' steps you see will depend on how far down the folder structure your required folder sits in the SharePoint hierarchy.

You will need to get the initial SharePoint path for the top of your hierarchy first - you can see how to do all this here:

https://docs.microsoft.com/en-us/power-query/connectors/sharepointfolder 

Pete

[LJRome]

It refers to C because I get to this particular SharePoint folder via OneDrive. I do have other queries that use SP urls, and the problems I reported here do not occur there. Plus we've been encountering other problems with SP and OneDrive, so my guess is that this problem may also have to do with that. But that's another story, marking this one as done. Thanks again Pete. 

[BA_Pete]

Hi @LJRome ,

Can you provide some more details please?

What do you mean by "Excel's been opening a few files"? Excel isn't autonomous, so what does this look like?

What error do you get? Is it the same each time or changes?

Where are the Excel files located? On your HDD, SharePoint, network folder? Are you using a gateway, built-in, or custom connector?

What does your query (M) code look like? Can you share it?

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/1447523 

Pete