Recently I have been working where I have multiple accounts, and I wanted to test the different access to the accounts when connecting via Excel.
I could not find any blog posts or videos on how to do this.
In my blog post I detail how to achieve this.
Connecting to the dataset using Excel
The method below will work when connecting to Power BI Premium/Premium Per user or Azure Analysis Services (AAS)
In my example I am going to connect to my Power BI Premium Per user dataset.
- First, I needed to get the Power BI Connection String from my Premium per user App workspace.
- I logged into the Power BI Service, went to my App Workspace.
- I then clicked on Settings.
- I then clicked on Premium and then clicked on Copy for my Workspace Connection.
- Next, I will connect to the data source from Excel and not via the Analyze in Excel option in the Power BI Service.
- I clicked on Data, then From Database, From Analysis Services
- In the Data Connection Wizard, I put in the Server name from the previous steps where I copied the connection string.
- I then also clicked on use the following Username and Password.
- I left the details blank as shown below.
- I then clicked Next.
- The first time connecting it prompted me to log in.
- I then selected my database and clicked Finish.
Changing the User Account in Excel
In the steps below is where I changed the user connection.
- In Excel I clicked on PivotTable Analyze and then selected Change Data Source and then selected Connection Properties
- In the Connection Properties Window, I then clicked on Definition, and I could then see the Connection string.
- The challenge here is to see that in the Connection String there is a property called “User ID=”
- To log in as another user I changed the “User ID” from being blank with “” to my user account I wanted to test which is email@example.com with the following below in the connection string.
- I then click Ok.
- I then get prompted to log in.
- Now I am connected with this new user to the same dataset.
Things to note!
What I have noticed is that if I have already logged in and authenticated to either of my accounts and I have a valid token I will not get prompted to log in again.
If I had to use a 3rd user account which is not logged in before I will be prompted.
When I tested the above steps after creating the test files I was not prompted to authenticate, which prompted me to put this into the blog post to hopefully cater for all things that could happen.
Questions and comments are most welcome. And thanks for reading.
About the Author:
Currently working as a Power BI & Data Analytics Consultant. I have over 10+ years’ experience working with data of any size and any source. By leveraging my skills I am able to provide insights into customers data quickly and efficiently. And this has enabled my customers to gain a better understanding of their business. Which relates to better sales, or significant cost savings.
I was recently awarded a Microsoft MVP award for Power BI. Power BI in my opinion is the logical choice in terms to creating insights, being cost effective and providing dashboards and reports on Web, Mobile or in Apps out of the box.
Quevauvilliers, G. (2021). Changing the User Account in Excel when connecting to Power BI Premium/Per User or Azure Analysis Services. Available at: https://www.fourmoo.com/2021/02/11/changing-the-user-account-in-excel-when-connecting-to-power-bi-premium-per-user-or-azure-analysis-services/ [Accessed: 7th March 2021].
Find more great Power Platform content here.