DataSource.Error: An IO error occurred while trying to access the file...

[ccsrtw]

I'm trying to create a variable query that outputs the name of the file most recently modified in a certain folder. I actually accomplished this yesterday and the output was successful, but when I refreshed the query today I received the following error:

DataSource.Error: An IO error occurred while trying to access the file 'FilePath'.

Details:

     FilePath

Code:

let
    //Choose folder
    Source = Folder.Files("\\xxx-my.sharepoint.com\personal\xxx\xxx\xxx\xxx\xxx\"),
    //filter out all but Excel files
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
    //sort in descending order by date modified
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date modified", Order.Descending}}),
    output = #"Sorted Rows"{0}[Name]
in
    output

Again, this was actually working yesterday. I still have complete access to the network folder that is trying to be accessed, so I don't think it's a permissions issue.

Any ideas?

[v-qiuyu-msft]

Hi  @ccsrtw,

As the original issue is solved and this thread is a little old, you can create a new thread so that communities can aware it and help you.

Thanks for your understanding and support.

Best Regards,
Qiuyun Yu

[v-qiuyu-msft]

Hi @ccsrtw,

From the error message, it seems the file can't be accessed. As you mentioned you have permission to access the file, the issue can be caused by that the file is occupied by other programs or other users. Please use Handle to find all process accessed this file, then kill them to refresh query in desktop to see if issue persists.

Best Regards,
Qiuyun Yu

[ccsrtw]

Hi Qiuyun,

I downloaded Process Explorer (Handle w/ GUI), but I honestly have no idea how to identify which processes (if any) to kill. I killed anything related to Excel, but that hasn't helped. Any other tips?

Appreciate the help!

[v-qiuyu-msft]

Hi @ccsrtw,

Would you mind recreating a .PBIX file use the same data source to check if the same issue occurs?

Best Regards,
Qiuyun Yu

[ccsrtw]

@v-qiuyu-msft - I created a .pbix file and copy/pasted the code into a blank query:

let
    //Choose folder
    Source = Folder.Files("\\PATH"),
    //filter out all but Excel files
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
    //sort in descending order by date modified
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date modified", Order.Descending}}),
    output = #"Sorted Rows"{0}[Name]
in
    output

Received an identical error:

DataSource.Error: An IO error occurred while trying to access the file '\\PATH'.

Details:

     PATH

Really odd. Frustrating that this worked initially and now isn't for no apparent reason!

Again, appreciate all of your help.

[v-qiuyu-msft]

Hi @ccsrtw,

I mean recreate .pbix is to click Get Data and choose Folder data source to retrieve data, instead of pasting original Power Query. I want to check if the issue is related to the folder path or Power Query. Please let me know the test result.

Also please try to update the desktop to the newest version 2.40.4554.463.

Best Regards,
Qiuyun Yu

[ccsrtw]

Morning@v-qiuyu-msft,

I updated to the latest version. Went through the Get Data > Folder process and received the exact same error. Does this imply an issue with the path?

Thank you for following up!

[v-qiuyu-msft]

Hi @ccsrtw,

Did you add any new files into this folder? Except Excel file(.xls/xlsx), Power BI file(.pbix) and Comma Separated Value (.csv) file, please remove other files then try again. Make sure all files are not opened with other applications.

Best Regards,
Qiuyun Yu

[ccsrtw]

@v-qiuyu-msft - thanks for sticking with me!

I did everything in your last post, but it's still hitting me with the same error.

After some more digging, I discovered this post: https://medium.com/@Konstantinos_Ioannou/onedrive-powerbi-desktop-use-valid-paths-to-import-data-sto...

I was able to import the file itself via Get Data > Web:

let
    Source = Excel.Workbook(Web.Contents("http://companydomain-my.sharepoint/personal/user_companydomain_com/Documents/Folder/File.xlsx"), null, true)
in
    Source

So now all I need to do is step back a bit and get the name of that .xlsx file as the output. I tried:

let
    Source = Folder.Files(Web.Contents("http://companydomain-my.sharepoint.com/personal/user_companydomain_com/Documents/Folder"), null, true)
in
    Source

but no go.

[v-qiuyu-msft · Accepted solution]

Hi @ccsrtw,

It seems folders exist in SharePoint online, please try the SharePoint Folder data source instead of Folder data source. See:

Best Regards,

Qiuyun Yu

[ccsrtw]

@v-qiuyu-msft - that did it! Thank you! Wasn't able to get past the user level of the sharepoint source (company-my.sharepoint,com/personal/user_company_com/), but it did list all of the files in that directory so just needed to do a bit of filtering to get the one file I wanted.

Thanks again!

Per usual, this led into another issue. My second query which is going to use the just solved output ("MyFileName") as part of the source (Source = Excel.Workbook(Web.Contents("https://company-my.sharepoint.com/personal/user_tegna_com/Documents/x/xx/xxx/" & MyFileName), null, true),) is now spitting out this error:

Formula.Firewall: Query '2nd Query' (step '...') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Trying to apply this solution from Ken Puls, but no luck so far: http://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/.