Forum Discussion
Helper IChange file path from folder to OneDrive
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.
Helper IHi...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.
Solution SageHey 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.