Power Query [DataSource.error] not finding a file that I've moved preventing a refresh

[kevsvette]

I have setup a query & function that referenced a file path to a folder that I weekly dropped files into & updated my query that combines these 2020 files. Now that we're in 2021 I have moved those 2020 files to a different folder and started putting 2021 files in that same defined path which is used as a repository file which is generically named. When I refresh the query now I'm getting a [DataSource.error] Could not find file... with the correct path that ends with a file that I've moved. As this file no longer resides in the folder & I do not want them in my combine query the query will not refresh because it cant find that file. If I move it back into the folder it returns the same error with the next missing file. I've moved multiple files and will keep getting this error until all the files are back into the folder. When I moved all the 2020 files back into the folder it will refresh correctly.

I've gone to the Data tab >> the Data Source Settings & the path is correct for where I'm putting in the files I need combined. Just to test I browsed right back to the same file path & I'm still getting the same error.

I'm at a loss. Because this is company info I cannot upload an example. Has anyone ever had this problem & is there a resolution. I am using Excel 2016.

 

[denzo36]

It is hard to see without seeing any data or screenshots of your query but what I imagine is happening is that the file path has been hard coded into a step further on in your query so isn't dynamically changing when you move the file. When the query looks in the old location (which has been hard coded) it's not there so returning an error. By hard coded I mean Power Query inserts the location into one of your steps when you use the GUI commands.

I would recommend going through each step in your query and click the gear icon and check the formula bar and see can you work out if this is the case

 

[kevsvette]

I found a work around to remove error files for those old files that now longer exists in the repository folder.