data query

[anaorca1990]

Hi, i have a question regarding the data source of POWER BI, i used EXCEL to develop the data and then i conect the tables of excel with the queries on power bi, the problem that i have is that the excel spreadsheet that i use is save in my computer, but i would like to save it online maybe in sharepoint? so other users that access to it and change from there if need it. 

hope i explain myself.

thank you 

[ChrisMendoza · Accepted solution]

@anaorca1990,

Yes, I have several Excel files saved in a SharePoint site that others update the tables in, so this is doable. If I recall, the difficult part is re-establishing the filepath/URL since you've likely already saved to your local filepath. That being said, it is not difficult to re-establish those connections.

You probably have something like the below in your Source:

= Excel.Workbook(File.Contents("C:\Users\yourUsername\Desktop\Book99.xlsx"), null, true)

You'll then need to save that file to your Sharepoint and change the Source to the Site URL to something like:

= SharePoint.Files("https://your.sharepoint.com/someFolder/", [ApiVersion = 15]),

You'll have to change the Navigation Applied Step as well.

*Make sure to SAVE all your Advanced Editor steps in a txt file because you will likely lose the next steps when you insert/change the Navigation* you should be able to paste them right back in after you remove the "old" Source & Navigation steps.

Save a backup first then try it out to see how it works.

[ChrisMendoza · Accepted solution]

@anaorca1990,

Yes, I have several Excel files saved in a SharePoint site that others update the tables in, so this is doable. If I recall, the difficult part is re-establishing the filepath/URL since you've likely already saved to your local filepath. That being said, it is not difficult to re-establish those connections.

You probably have something like the below in your Source:

= Excel.Workbook(File.Contents("C:\Users\yourUsername\Desktop\Book99.xlsx"), null, true)

You'll then need to save that file to your Sharepoint and change the Source to the Site URL to something like:

= SharePoint.Files("https://your.sharepoint.com/someFolder/", [ApiVersion = 15]),

You'll have to change the Navigation Applied Step as well.

*Make sure to SAVE all your Advanced Editor steps in a txt file because you will likely lose the next steps when you insert/change the Navigation* you should be able to paste them right back in after you remove the "old" Source & Navigation steps.

Save a backup first then try it out to see how it works.

[anaorca1990]

hello 

sorrry i have another question what if im making a new dashboard in power bi, i havent been able to create the connection with sharepoint.

[ChrisMendoza · Accepted solution]

@anaorca1990,

I don't understand. You've tried to do as I suggested and were unsuccessful and now you're wanting to know if you should start over?

If you're making a new Dashboard in Power BI then you still have to Get Data so you're likely still going to have an issue making the connection to the SharePoint.

Did you get an error message? Share Please.

My best guess is that you included subfolders in your URL, The Navigation step in the Query Editor is how you get to the file to get to the tables.

So if your SharePoint URL looks like:

https://your.sharepoint.com/someFolder/a_whole_bunch_of_stuff".aspx

You should only need to input the root in the above window:
 

https://your.sharepoint.com/someFolder/

Hope that helps.

[anaorca1990]

thank you for your answer, also is it possible to use one drive as well?

[ChrisMendoza]

@anaorca1990, 

Haven't tried. Looking at the connection option for Get Data I see a MS Exchange Online. Maybe that will work?