Connect Excel Pivot to a Power BI Desktop

Technical Value

With DAX Studio you can write DAX Querry against a SSAS Tabular, a Power Pivot Model or an instance of Power BI Desktop. Some time ago I opened DAX Studio for doing some querying in DAX against a Power BI Desktop instance.

Connect to a Power BI Desktop instance with DAX Studio

Connect to a Power BI Desktop instance with DAX Studio

After a while the connection was disconnected, maybe a timeout. When I tried to refresh the connection, DAX Studio showed me a localhost adress to an Tabular Server instance.

Reconnect to Power BI Desktop instance

Reconnect to Power BI Desktop instance

I tried this connection and realized that DAX Studio had reconnected to my Power BI Desktop Tabular instance. I had always asked myself how it is possible that DAX Studio can connect to the Power BI Desktop data model. With this little clue I started to understand what the Power BI Desktop actually is. A kind of mini SSAS server running inside this tiny program. AMAZING! For some IT-Pros this might be nothing new, but for me who is working with Power BI every day it opened many new ways for analysis. For example, I sometimes wished that Excel Power BI had the same improved Features that the Power BI Team is bringing to Power BI Desktop every week.

I first tested if it is possible to connect with Excel to my local Power BI Desktop SSAS instance. I only have the German version of MS Excel, sorry for that (go to Data/From Other Sources and enter the server name (2) and create a Pivot Chart or whatever you want).

Connect with Pivot to Power BI Desktop

Connect with Pivot to Power BI Desktop

It actually worked out of the box. I got access to the data model and could use it, as if a full SSAS Server is running in the Background.

Pivot on Power BI Desktop data model

Pivot on Power BI Desktop data model

For most of the Power BI users this is not relevant, because they can also use the native Excel Power BI plugins to create a data model with Power Query and Power Pivot. But I just want to explain an interesting, maybe strange use case.

Now I can use Power BI Desktop as mini tabular prototyping server. The SSAS Tabular is of course the better choice for a big environment, but SSAS Tabular does not have Power Query and I know no better tool for fast data prototyping than Power Query. Excel Power BI could be used instead, but Excel Power BI is not that stable like as Power BI Desktop, especially for complex models. Also some of the newer comfort functions are missing, even in Excel 2016. And I can use different front ends for browsing my Power BI Desktop model, for example Excel, Reporting Services SQL Server Management Studio. This scenario is only suitable for prototyping and testing because there is no way to integrate a user authentication.

Connect with SQL Server Management Studio to Power BI Desktop

Connect with SQL Server Management Studio to Power BI Desktop

I tried to dig a little bit deeper, because there is one problem with every new instance: Power BI Desktop gets a new port. So you have to change the connection string inside your external tool if you have to restart the Power BI Desktop instance. The used port can be seen with TCPView. TCPView is a little Microsoft Tool which shows all open ports in and outgoing traffic on your network card. Here you can see the port that is used by my Power Bi Desktop instance.

Open ports of Power BI Desktop

The second thing I found out is that not the PBIDesktop.exe is listening for incoming traffic, but the msmdsrv.exe, which is also used by SQL Server SSAS. But I found no configuration settings like  it is possible with the SQL Server. At the moment I don’t know how to go further, if there is anything new I will give you an update.

Have fun with your own mini tabular model server.


Mo, 02.05.2016 - 16:12

sorry for my late respond. It should be possible to call the power bi desktop instance from outside your localhost (network). I have done some tests:
1. I tested if Power BI Desktop is capable to support mutli users, and yes it is. It's a bit tricky to allow a secound user on your Power BI Desktop Instance (use SQL Server Managment Studio to add users to the model)
2. Connect to the model via network seems to be not so easy. We tried it about an half an hour, but we weren't able to get through the open port. We think this has nothing to do with Power BI Desktop. More probale it is a firewall or network problem.

<strong>So I have no solution for the problem, I'm sorry.</strong> At the moment you can only connect with software installed on your localhost.

Imke Feldmann
Di, 08.11.2016 - 18:22

Hi Lukas,
you can use this M-code in Power Query to retrieve the current port number automatically:
Lines.FromBinary(Table.SelectRows(Folder.Files(“C:\Users\..YourUserName..\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces”), each ([Name] = “msmdsrv.port.txt”))[Content]{0},null,null,1200){0}

see: http://biinsight.com/connect-to-power-bi-desktop-model-from-excel-and-s…

Fr, 11.11.2016 - 13:27

Hi Imke,

that is a great Blog post thank you!

Sa, 07.05.2016 - 07:14

Lukas, it seems we independently got to same or at least very similar conclusions. I've been trying to connect to Power Pivot models using Python for quite some time, but then finally figured it out when looking into Power BI and how it communicates with the SSAS engine... Have a look here (http://stackoverflow.com/a/36878268/5803031) and here (https://github.com/akavalar/SSAS-on-a-shoestring). Basically, one can have a free-as-in-free-beer Analysis Services server without spending a ton of money on the MS SQL Server. Anyway, glad to see I wasn't the only know who was intrigued by all this!


PS - I need to give credit where credit is due: I started tinkering with the msmdsrv.exe approach after I saw these two posts (http://www.dbaglobe.com/2015/08/how-to-change-ssas-port-number.html and http://www.dbaglobe.com/2015/08/start-sql-server-analysis-service-in.ht…).

Fr, 15.04.2016 - 08:28

This Solution can't connect a another host‘s power bi desktop instance via ip,only can access localhost instance .it's right?

Neuen Kommentar schreiben

Der Inhalt dieses Feldes wird nicht öffentlich zugänglich angezeigt.


  • Keine HTML-Tags erlaubt.
  • HTML - Zeilenumbrüche und Absätze werden automatisch erzeugt.
  • Web page addresses and email addresses turn into links automatically.
Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden