Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Mee2105
Frequent Visitor

Connecting Excel in SharePoint to Power BI

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

1 ACCEPTED SOLUTION
goncalogeraldes
Super User
Super User

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:

goncalogeraldes_0-1699371886482.png

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):

goncalogeraldes_1-1699372059987.png

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

4 REPLIES 4
goncalogeraldes
Super User
Super User

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:

goncalogeraldes_0-1699371886482.png

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):

goncalogeraldes_1-1699372059987.png

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

 

OMG, thank you so much !! this works

chonchar
Helper V
Helper V

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

 

@Mee2105 
This is the video that helped me

 

https://www.youtube.com/watch?v=OCfv6beXjjQ 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.