This page shows how to connect the data for Power BI report when you have data source file, such as Excel output from query or csv file or other type of file. Data sources used in Power BI should always be stored in the UMS Power BI HUB library, not on a user’s local machine. When connecting to data in Power BI, DO NOT use either the Get Data > Excel or Get data >Folder methods. This will create a connection to the local versions instead and refreshes will fail once the report is published to the service or if a colleague tries to refresh the data in the .pbix file.

If you would like to combine multiple files, go to the Connect to Multiple Data Source Files page.

Connect to a file on SharePoint Site in Power BI

Make sure that the data file is available in the SharePoint Document Library. If you don't have file(s) in SharePoint, go to Sync SharePoint User Libraries to Your Computer for more information.
In Power BI Desktop, select Get Data > More > SharePoint folder and click ‘Connect’.
Enter this URL: https://umainesystem.sharepoint.com/sites/UMSPowerBIUsersCommunity_O365 (The URL has to be root of a SharePoint site.)
Proceed to log in - Use Microsoft Authentication. “Sign in” if you haven’t done so. (This process is for the initial time only.) If the login window doesn't appear and you receive an error message, follow the steps in the "Access to the resource is forbidden" section on the PBI Troubleshooting page.
Here, you will see all of the files across all of the SharePoint Libraries to which you have access. Click ‘Transform Data’.
Choose file: The list contains all of the files in the SharePoint library to which you have access, so you will need to filter them. Choose one of the ways below (Option a-c) to filter files.

Option a: Filter by the "Name" column. Check the box for file you are looking for. Note: When the file name contains special characters (such as %), the transformation will not go through,

Filter by name

Option b: Filter by the "Extension" column. Choose the file type you are looking for.

Option c: Filter by the "Folder Path"

7. After you filter file and find the file, click on the ‘Binary’ under Content column.

binary

8. Click the 'Table' for the appropriate row. (In this example, the user would like to import data from "Sheet 1", so click the row.)
table link

9. The data will show up automatically. Click the 'Use First Row as Headers" if the header showed up on the second row.

first row as a header

10. Do more transformation if necessary.

SharePoint Data Refresh (Data Source Credentials)

After publishing a report to the Power BI Service, Data Source credentials for all reports that connect to SharePoint or other online data sources need to be configured. This is a one-time step for each report, after which all workspace members with admin/edit capabilities will be able to refresh data on demand and set up scheduled refreshes.

After publishing the report for the first time, go to the workspace in Power BI Service. Proceed to Datasets - dataflows Tab, hover over the dataset, and click on the ellipsis to navigate to the Settings Option.
Select Edit credentials from Data source credentials > SharePoint.
Set Authentication method to "OAuth2" and Privacy level to "Organizational". Click "Sign in".
Run manual refresh to make sure that everything has been successfully connected.

HELP IS AT HAND!

Link to compose email for help with website.

Question, comment, critique? We are here to help and we strive to make our site a comprehensive, user friendly experience. Contact us at DARTS@maine.edu.

DARTS : Connect to Data Source file

Connect to a file on SharePoint Site in Power BI

Attachments: