How To: Load Excel or CSV Files into Power BI
By
Tito Perez & Swati VishwanathanUse Case
Use this technique when:
- If you wish to set up scheduled refresh in Power BI service, you must use sharepoint link to the file instead of a path to a machine drive.
- If you want to transfer the dataset refresh or update to another person on the team, they cannot refresh the dataset if there is a connection back to your local machine.
Pre-Requisites
- Access to excel or csv file
- Permissions to publish to PBIX workspace (if your intent is to publish the report )
Procedure
Get the link to the file
There are different type of links you can use
- Excel (stored in OneDrive/SharePoint)
- OneDrive
- SharePoint
OneDrive Link
- Right click on file in the file explorer.
- Navigate to OneDrive> Copy Link
- Copy over this link to a notepad or any text editor.
- Remove the following part from the string: ‘?web=1’. Your path should now simply end with the format of your file, eg. Csv, xlsx.
SharePoint Link
- Open file.
- Navigate to: File > Info > Copy Path:
- Copy over this link to a notepad or any text editor.
- Remove the following part from the string: ‘?web=1’. Your path should now simply end with the format of your file, eg. Csv, xlsx.
Add in Power BI Desktop
- Get Data> Web> Paste link to file.
- You will be prompted to authenticate. Select Microsoft / Organizational (depending upon which prompt you see) and follow prompts for authentication.
- Click Connect. Confirm that you are connecting to the sharepoint link for the file. At times it defaults to the top level sharepoint folder.
- Once you are connected, you are ready to build your report.
- Confirm you are able to refresh the PBIX file locally.
- To refresh this dataset in the service, navigate to dataset settings. The sharepoint links can be refreshed using 0Auth2 credentials.
Options
TODO: