Connect to Data Source file

Body

This article shows how to connect the data for Power BI report when you have a data source file, such as an Excel output from a query or csv file or other type of file.

Instructions

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.

Steps

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 the 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 the 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 when narrowing down the list of files

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

showing for using extension function to narrow down the list of files

Option C: Filter by the "Folder Path"
showing the folder path option when narrowing down the list of files

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

showing the location of Binary link under the Content column

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.)
showing the location of table link under data column

9. The data will show up automatically. Click the 'Use First Row as Headers" if the header shows up on the second row.
showing the location of first row as a header function

10. Do more transformation if necessary, and close and apply when it is done.

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 the Authentication method to "OAuth2" and the Privacy level to "Organizational". Click "Sign in".
Run a manual refresh to make sure that everything has been successfully connected.

Environment

Power BI