Forum Discussion
Helper IChange file path from folder to OneDrive
Greetings all,
I have a BI project that currently loads data from file folders (containing Excel files) into Power Query tables. The challenge is that I am am the only person that can access/update the folder data but I need to allow others to do so. The plan is to do this through OneDrive however the current file paths do not go to OneDrive, but go to my Desktop. Is there a way to change the paths to OneDrive without having to rebuild the entire project? I have tried to do this by simply changing the paths but it will not allow me to connect to OneDrive. If I manually copy the paths from OneDrive I get an error.
Fairly new to BI so any assistance would appreciated.
Thanks in advance!
Hiya thechad13 , there is a way to change the path but really it's more like changing your Source applied step in Power Query as it's more than just the path that has to change.
- Start by uploading your file to OneDrive.
- Once uploaded open the OneDrive file so that it loads into Excel
- In Excel, click File, then Info. You should see an option for Copy path. Click it.
- You'll give a path that looks something like this... https://companyname-my.sharepoint.com/personal/username/Documents/Filename.xlsx?web=1. Paste the link somewhere then remove ?web=1 part. So it'll look something like this https://companyname-my.sharepoint.com/personal/username/Documents/Filename.xlsx
- Open your Power BI report and go to Power Query.
- For the data source you're trying to change click on the Source Applied step.
- As a side step if you do not see the Formula Bar then click View at the to of your Power BI then check the option for Formula Bar
- In your Formula bar your Source will look something like this =Excel.Workbook(File.Contents("C:\Users\username\Downloads\FileName.xlsx"), null, true). Change File.Contents to Web.Contents.
- Change the local path to the copied OneDrive path from earlier (make sure to keep it within double quotes). It should look something like this... =Excel.Workbook(Web.Contents("https://companyname-my.sharepoint.com/personal/username/Documents/Filename.xlsx"), null, true)
That will change the source path from your local file to OneDrive.
7 Replies
- Watsky
Solution Sage
Hiya thechad13 , there is a way to change the path but really it's more like changing your Source applied step in Power Query as it's more than just the path that has to change.
- Start by uploading your file to OneDrive.
- Once uploaded open the OneDrive file so that it loads into Excel
- In Excel, click File, then Info. You should see an option for Copy path. Click it.
- You'll give a path that looks something like this... https://companyname-my.sharepoint.com/personal/username/Documents/Filename.xlsx?web=1. Paste the link somewhere then remove ?web=1 part. So it'll look something like this https://companyname-my.sharepoint.com/personal/username/Documents/Filename.xlsx
- Open your Power BI report and go to Power Query.
- For the data source you're trying to change click on the Source Applied step.
- As a side step if you do not see the Formula Bar then click View at the to of your Power BI then check the option for Formula Bar
- In your Formula bar your Source will look something like this =Excel.Workbook(File.Contents("C:\Users\username\Downloads\FileName.xlsx"), null, true). Change File.Contents to Web.Contents.
- Change the local path to the copied OneDrive path from earlier (make sure to keep it within double quotes). It should look something like this... =Excel.Workbook(Web.Contents("https://companyname-my.sharepoint.com/personal/username/Documents/Filename.xlsx"), null, true)
That will change the source path from your local file to OneDrive.
thechad13Hi...thanks for your response. This appears to be pointing to a specific xlsx file as a resolution however, I need a resolution that points to a folder that may contain multiple Excel files within that is pulled into BI.
As an example, I have a folder that currently contains 4 Excel files (each represents a different year of data) and I pull the data from that folder into BI as one table, not one specific Excel file.
- Watsky
Hey thechad13
My apologies for not reading correctly... this still can be done. It's not going to be a simple modification to your exsiting source but do able. For this you will use the SharePoint Folder connector.
I will make the asumption that you have a folder on your local drive which is the same as on OneDrive.
- Start by taking the url from your OneDrive which can be done with the instructions before or you can right click on your OneDrive icon in file explorer then clicking View Online. Your url will look something like this. https://companyname-my.sharepoint.com/personal/username/_layouts/15/onedrive.aspx
- To use SharePoint Folders we only need the link to include the primary location of the SharePoint so you'll want to delete the everything after your username. It'll look something like this https://companyname-my.sharepoint.com/personal/username/
- Now, create a whole new data source in Power BI using SharePoint Folder. What you'll see is all of the files inside of your OneDrive. This works a bit different than other connectors where you're not going to see sub-levels where files are inside folders. Instead, you will see a column called Folder Path (found in the last column).
- Filter the Folder Path to the folder where your files are located. You should be left with just the files you are going to be cominbing. If you happen to have other files inside of the folder you don't want to combine then you will want to filter it down even more.
- Now, go into advanced editor
- Your advanced editor should look like this: Copy the two lines under let and before in (the Source and Filtered Rows lines) .
- On your original data source, click Advanced Editor. Remove the Source row and paste in the two lines that were copied from the new data source.
- At the end of your second row (#"Filtered Rows") you will need to put a comma. Then on the next line (For me that is #"Filtered Hidden Files1") You will find the word Source in the line. Change that to #"Filtered Rows". This is what my third line looks like: #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), changing it to this #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
- Click Done. Make sure your changed original data source loads all of the applied steps. Once confirmed you can delete the other data source.
That should be it. If you run into any errors screenshot the error and your M code in the advanced editor and I'll help you through it.
- thechad13
Thanks for this...I think I'm almost there. However, I have used #"Filtered Rows" elsewhere as an applied step. Is there a way around that? Is it similar to VBA where I could change the instructions you gave me to #"Filtered Rows2"?
- Watsky
Yes you can name differently. Just make sure you change it on the third line as well.
- thechad13
Hey...just a shout out to let you know that I got this to work. Once I did it a couple times it wasn't too difficult. Thanks so much for your assistance!!!
