Why do I want to connect Sharepoint / OneDrive to Power BI?
Do you often wonder how to connect google sheet to Power BI and achieve automatic refresh? You can do a few tricks to make that happen, but it will either involve not secure method (publishing the web to the public) or require using Bigquery (***update: as of 2022, Power BI has a beta version of google sheet connection so you can use that to connect google sheet). If you have to make the google sheet private and secure but do not have access to bigquery, then using Microsoft’s version of google sheet would be your best choice.
Microsoft has its own online version of excel you can use. You can create the file, and it will auto save just like google sheet, and maybe this will be easier for you if you are more familiar with excel! All you have to do is to create or upload a file within sharepoint (same as oneDrive) and then connect it to Power BI. This will allow flawless automatic refresh on Power BI App. So let’s look at how to connect.
How to connect sharepoint & OneDrive to Power BI
1. Get data > click "More" > sharepoint folder
2. Add the site URL
Your site URL will be in this format:
https:// [Organization Name].sharepoint.com/sites/ [Team Site Name]
Wait, team site?
Yes – team site. It’s basically a shared google drive but for Sharepoint/OneDrive. FYI, I am using sharepoint and onedrive interchangeably because they are the same except sharepoint has a bit more features in addition to oneDrive. For more information on Sharepoint vs. OneDrive, check out this blog.
What if our org does not have one?
If you do not have a team site, then you could use your own personal (individual) sharepoint to connect. I do not recommend this though as you will be the only one that can connect to sharepoint. When I tested this out, I could not give other team members access to my entire sharepoint folders, only by individual folders. And I could not connect the individual folders to Power BI, only my entire sharepoint; which means, other people cannot connect their Power BI to the files in your sharepoint folder like you can.
Ok.... any other option?
If you are encountering this lack of a team site issue, you have two additional options: 1. you can create a team site yourself (it’s easy! Instruction below) or, 2. you can create a dataflow using your personal sharepoint connection, so others can connect to the dataflow instead (this option is actually recommended even when you connect your team site for efficiency and data governance). **the best scenario would be to create a team site and then using dataflow for files that many reports will connect to. If a file is only for one report, dataflow is not necessary. Team site will ensure that all your team members have an access to the files.
Real quick: how to create a team site
Go to your personal sharepoint (OneDrive) page. On the left, you will see [create shared library]. Click it and name it, and share it with your team!
3. Sign in using your Microsoft account & set the right level
It will pop up a login window to confirm your account.
Set the right level
Make sure the “level” to apply the setting to. The dropdown will give you a few options. You can do either:
https:// [Organization Name] .sharepoint.com/
https:// [Organization Name].sharepoint.com/sites/ [Team Site Name]
Since other teams can start creating more sites and it can be cluttered, I am going to use the option B.
4. Connect and navigate to the file
Once you click [Connect], you will see multiple files. Click [Transform Data] so you can navigate to and choose the file you want to access. Once you find it, click [Binary] of the file you want to access.
Once you click [Binary], you are at the file location. Click [Table] or expand button to see the content of the file.
Now you should see your file! Do the necessary cleaning like “Use First Row as Headers” and ensure the null rows are gone, etc.
댓글