Power Query returns null in some (but not all) columns when the name of the file is changed, why?

Question

I have some files that I have combined in power query. These are 9 files that will need to be downloaded at least once a day so I could do without having to rename them. I then merge these with a couple of other files. The 9 files sit in a folder so I access the folder rather than the individual file and these are stored on SharePoint.

I set up the queries that I needed and it worked perfectly but this was when I was accessing SharePoint via my local drive so no one else could do the refresh.

I then looked to replicate this so others could refresh but I notice my current version of excel does not have the Get Data, SharePoint Option but I found a solution that involves finding the file path in PBI and then copying and pasting this into the excel version of power query. This again works perfectly, although it's very slow.

My issue now is that I just want to be able to download the 9 files into the folder and not rename them as I had done before (now I know they need to be downloaded more it's quite time consuming and overwriting the files is open to errors) but when I do this and refresh, the refresh doesn't pull through the data in the first 3 columns (it does bring through the column headers). Unfortunately the data in the first column is my identifier that connects the data to the other files that I merge with so I really need that data. I've tried starting a new excel doc from scratch with the downloaded files without the name change but it's still not pulling the data. The only way I can get the data to pull though is if I rename the files to the original names or to run it through my local access. Can anyone help?

Answer 1

Hi @AW,>> I set up the queries that I needed and it worked perfectly but this was when I was accessing SharePoint via my local drive so no one else could do the refresh.

Do you use OneDrive application to sync SharePoint library locally or other method to access SharePoint via local drive?

Power Query relies on the path and file name of the file to identify each file. If the file name changes (even for files in a folder), Power Query may lose reference to the file.

My issue now is that I just want to be able to download the 9 files into the folder and not rename them as I had done before

To achieve this, you may need PowerShell or Power Automate. The reference: https://stackoverflow.com/questions/69848711/automate-download-file-from-sharepoint-without-installing-any-software

https://learn.microsoft.com/en-us/answers/questions/788342/powershell-script-to-download-specific-folders-fro

https://www.sharepointdiary.com/2016/09/sharepoint-online-download-file-from-library-using-powershell.html

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---