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 gilbert@simplemoo.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.

Summary

Thanks for reading and I hope that this makes it easier to change user accounts when using Excel to connect to Power BI Premium, Premium per User or AAS.

Questions and comments are most welcome. And thanks for reading.