Solved: Connecting Excel in SharePoint to Power BI

Mee2105 · ‎11-07-2023

Hello All,

I am new to Power BI but having said that I have read the plausible solutions to my issue on this community and yet been unsuccessful.

I have created a PowerBI dahsboard using an excel file on my local but now I have placed it on the SharePoint and am wanting to connect it to it. My questions are :

1. I am selecting 'SharePoint folder' in the get data section. Should I be using 'Web'?

2. Whenever I paste the entire URL, it dosent accept and throws errors.

Could anyone give me points on how to connect the excel on my sharepoint to my Power BI.

Many Thanks

goncalogeraldes · ‎11-07-2023

Hi @Mee2105 ,

When you are connecting to Sharepoint, the connection string is the sharepoint HOME link (not the final one - the file destination). Having said that, go to your sharepoint site and navigate to its homepage and copy that link, which should look something like:

Use this in the connection string and within PBI navigate to the desired file (this will give the full content of the SP site so you may need to filter to find your file):

Finally, when you find your file, open the "Binary" column and it will open the file.

Extra: you can delete any filtering steps added to the query as they are irrelevant to PQ itself.

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

View solution in original post

goncalogeraldes · ‎11-07-2023

Hi @Mee2105 ,

When you are connecting to Sharepoint, the connection string is the sharepoint HOME link (not the final one - the file destination). Having said that, go to your sharepoint site and navigate to its homepage and copy that link, which should look something like:

Use this in the connection string and within PBI navigate to the desired file (this will give the full content of the SP site so you may need to filter to find your file):

Finally, when you find your file, open the "Binary" column and it will open the file.

Extra: you can delete any filtering steps added to the query as they are irrelevant to PQ itself.

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

Mee2105 · ‎11-08-2023

OMG, thank you so much !! this works

chonchar · ‎11-07-2023

@Mee2105 This is going to be rough to explain because I cannot show you our SharePoints.

But you select under the "More option" and select SharePoint. But the problem is that you cannot target specific folders immediately, so you must connect to all of them.

Make sure the Link is basically the homepage. You may have to try a few options to get it to work.

Then, once you get connected, you will see ALL OF THE files in sharepoint. Make sure you filter them with the little action dropdown and then you can load and transform from there.