Quick steps for connecting to SharePoint using Power BI Desktop

[monaraya]

A.Connector details and URL to be used:

a.Format of URL for each connector: - 

1.SharePoint Folder uses SP Root/Home URL of Site: https://demo.sharepoint.com/teams/BI2

URL can be found if we browse to SharePoint Site's Home page as shown in below screenshot: -

2.SharePoint Online List uses SP Root/Home URL of Site: https://demo.sharepoint.com/teams/BI2

3.SharePoint On prem List uses SP Root/Home URL of Site: https://pXXXXl.XX.XXX.qa/XXXXXX/itXXXX

4.Web Connector for Excel in SPO: https://demo.sharepoint.com/teams/BI2/Shared%20Documents/Financial%20Sample.xlsx

b.Purpose of each connector:

1.SharePoint Folder:

1. We use this when we would like to combine multiple files present in SharePoint Online Site and can be used for on prem SharePoint folder as well.
2. We put in the SharePoint Site root URL in Power BI.
3. For example, all the excels present in SharePoint Online/On prem site can be combined and used as per: https://powerbi.microsoft.com/en-us/blog/combining-excel-files-hosted-on-a-sharepoint-folder/

2.Web Connector for excel in SPO:

1. When we want to load one excel file from SharePoint Online, we use Web connector and put in path/link of that Excel.
2. This does not combine many excels. One connector can connect to one excel.
3. We would have to use Web connector multiple times so as to connect to multiple excels.

3.SharePoint Online List:

1. SharePoint Online list can be created in SharePoint as per: https://support.office.com/en-us/article/create-a-list-in-sharepoint-0d397414-d95f-41eb-addd-5e6eff4...   

ii.We use this when we have a list in SharePoint Online and we need to import it in Power BI Desktop.

iii.Ref: https://social.technet.microsoft.com/wiki/contents/articles/40040.power-bi-two-way-to-get-data-from-...

4.SharePoint List:

1. Used for SharePoint On Prem List
2. Same connection steps as in SPO List.

B.Steps to connect to SPO:

1.SharePoint Folder: https://powerbi.microsoft.com/en-us/blog/combining-excel-files-hosted-on-a-sharepoint-folder/

2.Web connector:

• Connecting to an Excel file on SharePoint or OneDrive For Business with Power Query
• https://www.blue-granite.com/blog/power-bi-office-hours-sharepoint-files-lists-folders

1.Please browse to the Excel file in SharePoint Online -> Click on the 3 dots beside the file name -> Open the excel in App as shown in below screenshot: -

2. After the File opens on your local Excel App -> Click on ‘File’ on top left -> Info -> Click on ‘Copy Path’ as shown in below screenshot

3.From the copied link, remove “?web=1” and the resulting link should be like below:-

 https://demo.sharepoint.com/teams/BI2/Shared%20Documents/Financial%20Sample.xlsx

4.Open Power BI Desktop -> Get Data -> Web -> Paste the link from Step 3

5.Select Authentication as Organizational/Microsoft Account -> Sign In -> Connect -> The file should load.

Author: Srishti Sharma

Reviewer : Mounika Narayana Reddy