Import Data from Tabular Model in Excel Using a DAX Query

When you use Excel to connect to a Tabular database in Analysis Services, you get a PivotTable as a result. In this article, you learn how to import data in a table writing a DAX query than runs on Analysis Services.

A connection to Tabular in Excel is a connection to Analysis Services. The Excel wizard does not allow creating a connection including a DAX query, but with a simple change in the ODC file generated by the wizard, you can transform a connection to a cube into a DAX query that returns data in an Excel table. You start by creating a connection to Analysis Services, specifying the server name and the log on credentials. In the following example I use the TABULAR instance on the local machine, you have to write the correct serverinstance name for your database.