Power Query Connection to Excel PowerPivot Data Model

TL;DR

We can connect almost any data source in Power Query, but PowerPivot data model is not included in that extensive list of sources out of the box.

But with the help of the fabulous DAX Studio we can do it (although in my opinion it is still inconvenient and tricky) at least locally – from the same workbook in Excel or from Power BI Desktop.

All you need is to open your Excel workbook, run DAX Studio add-in and connect it to this workbook. Then you can just connect to the PowerPivot model as to SQL Server Analysis Services cube.

But this is an undocumented and extremely limited feature not supported by Microsoft, which can only be used under your own risk.

Step 1: Ready…

First, we need the Source. In other words, the Excel workbook with some data, loaded to the PowerPivot data model. If you can see a table in the PowerPivot window, it is good.

I have a workbook with these sample tables:

which form this simple data model:

In the ‘Sales’ table I have already created the calculated column to calculate cumulative total for the product by date:

And the simple measure:

The source of tables in PowerPivot model is the tables from this workbook, but they could also be from Power Query.

Second, we need DAX Studio add-in for Excel installed. If you are working with Power BI or PowerPivot and do not have DAX Studio installed, I have bad news for you… If so, or if you haven’t installed add-in for Excel, go to the https://daxstudio.org/, click the big green button to download the latest release and install it. During the installation, do not forget to install not only application alone, but the Excel add-in, too. It is necessary.

Step 2: Steady…

To connect Power Query to your workbook data model, open your workbook :). For some reasons I insist that only this workbook is opened! This and only this one. If several workbooks are opened simultaneously, there could be mess and problems.

Run DAX Studio add-in from the “Add-ins” ribbon group in Excel. After the DAX Studio starts, you will see connection window, where you must select the first option:

Click “Connect”. After the connection is established, do not close DAX Studio, it must be running. You can minimize it if you want.

Now all is ready to connect.

Step 3: Go!

Now go to the Power Query and connect to the «SQL Server Analysis Services database (import)».

We need to know the server address for it. If this connection was to some server or Power BI Desktop, we could then see its address in the bottom right corner of DAX Studio window:

But when connected to the Excel file, we see only this:

No server name, no address, no port number.

Now I reveal the main secret:

You need to use http://localhost:9000/xmla as the server name/address. That’s all.

Of course, you can also use this database name (it is necessary to write it down if you want to use custom MDX or DAX query in connection): “Microsoft_SQLServer_AnalysisServices”

How did I reveal this address is too TL;DR even for me 😊. You need to know that these server address and database name are always the same, at least, at the time when I write this post, so you don’t need to search for the server address every time.

When Power Query asks you about the data source credentials, just use your current Windows authorization (sorry if you cannot read the screenshot in Russian, just look which options I selected):

Ta-dam!!!

Now you see the model, all its calculated columns and measures as the OLAP cube. You can build any tables you need from it.

If you are familiar with MD cubes and their behavior, then you are ok 😊 If no, you will need some time to adjust to cubes logic and use.

Read PowerPivot model from Power BI

If for some reason you do not want to import Excel data model in Power BI, you can use this trick to connect to PowerPivot in Power BI Desktop. But again, only in import mode, no live connection.

All the steps are THE SAME.

Purposes

For me, this trick is one of the long “just because I can” list. To be honest, I do not see any useful applications for this connection today. It is too tricky, limited to manual refresh, and cannot be used across other workbooks. But if you have an urgent need to extract some data from the Excel data model to PQ, you can use this approach carefully. For example, you may need to do some data mashup operations in Power Query, but some of them are too complex in Power Query and have a very bad performance there (usually it is about mashups which require a lot of scans of the same table), or there are other reasons to do some calculations in DAX rather than in M. You can export mid-step results in the data model and perform required calculations in PowerPivot, then read it again in the Power Query.

Or you can use it to perform any suitable MDX or DAX query over your data:

I think that there could be other needs which could be satisfied with this trick and which I did not meet yet. If you have some in mind do not hesitate to share.

Cautions and known problems

1. This is undocumented
2. This is unsupported
3. This could change any moment, no guarantee.
4. When connecting from Power BI Desktop, be sure that only one workbook is opened – at least, only one workbook with data model in it
5. No, I do not know how to read PowerPivot model in another workbook. All my attempts failed; you only see the data model from this one workbook.
6. To get correct refresh order of your queries and tables, you need to do it manually (or via VBA macro). Clicking “Refresh All” button in Excel do not guarantee refresh order, so not all your changes could be reflected correctly. Or press “Refresh All” twice.

There are a lot of other interesting things and pitfalls with this connection, but I don’t want to spend too much time on it now. At least not until I see a good reason or a use case for it.

Good luck!