PowerBI report data source is Excel spreadsheet in Teams– Multiple users to access

[dd88]

PowerBI report data source is Excel spreadsheet in Teams– Multiple users to access the PowerBI report

PowerBI desktop app

Background

I created a PowerBI report for 1 user

The data source file is an Excel spreadsheet.  The files are in Teams, SharePoint, however most users prefer to work in File Explorer. The Excel file is in Teams. The user has synced Teams with File Explorer, and works on files in File Explorer. The PowerBI report is in the same Team. If there are new change requests, I make the amendments and update the PowerBI latest version report in the Team.  Syncing is on and the user automatically gets the updated PowerBI report.

This works well for 1 user.

Now, multiple users would like access to the PowerBI report.  When they open the PowerBI report, Errors display as unable to find the data source which is under the User 1 profile eg.

C:\Users\<User 1 name>\Company Name\TeamName\Excel spreadsheet.xlsx

What is the best way to set up, to allow multiple users to access and open the PowerBI report with the same data source Excel Spreadsheet? Note the Excel spreadsheet is not confidential.

TIA

[Dhairya · Accepted solution]

Hey @dd88 

As @Rabech specified you have to use the SharePoint folder as a data source and use it in the Power BI report as a data source, and to give access to the report to multiple users you can publish your report to the Power BI service and give access to multiple users. By doing so when data is updated or changed it will automatically be reflected to all users.

If this helps you then please mark my solution as accepted so that others can find it quickly while facing the same issue. Thank You!

[Dhairya · Accepted solution]

Hey @dd88 

As @Rabech specified you have to use the SharePoint folder as a data source and use it in the Power BI report as a data source, and to give access to the report to multiple users you can publish your report to the Power BI service and give access to multiple users. By doing so when data is updated or changed it will automatically be reflected to all users.

If this helps you then please mark my solution as accepted so that others can find it quickly while facing the same issue. Thank You!

[Rabech]

Hi dd88,

You should be able to use sharepoint folder as a data source instead of using the local path. After doing this you can find the excel file when you transform the data.

[dd88 · Accepted solution]

hey @Rabech and @Dhairya 

Thanks, my results are ..

In SharePoint, if I goto the exact folder location containing the files, copy the url
In PowerBI in the SharePoint folder Site URL, paste the URL
An error displays
This URL isnt valid. Please enter the sites root URL only

In SharePoint I go back to the Folder root, which is the Teams name only, copy and paste.

Displays all files under \Documents.

I select the Excel spreadsheet.
All tabs & data is available. I just need to tidy up the Table column names & data

How to connect to a data file in a SharePoint folder in Power BI - YouTube

All good, thank you