Reply
Regular Visitor
Posts: 22
Registered: ‎05-15-2018
Accepted Solution

data query

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 


Accepted Solutions
Established Member
Posts: 180
Registered: ‎03-22-2018

Re: data query

@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.

 

 

View solution in original post

Established Member
Posts: 180
Registered: ‎03-22-2018

Re: data query

@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.

 

sharePoint-siteURL.png

 

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.

View solution in original post


All Replies
Established Member
Posts: 180
Registered: ‎03-22-2018

Re: data query

@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.

 

 

Regular Visitor
Posts: 22
Registered: ‎05-15-2018

Re: data query

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

Established Member
Posts: 180
Registered: ‎03-22-2018

Re: data query

@anaorca1990

 

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

Regular Visitor
Posts: 22
Registered: ‎05-15-2018

Re: data query

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.

Established Member
Posts: 180
Registered: ‎03-22-2018

Re: data query

@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.

 

sharePoint-siteURL.png

 

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.