This post is also available in: de

Hits: 550

Data from the Power Pivot data model can be displayed in Excel in pivot tables or pivot charts. Sometimes, however, it is necessary to create a DAX query and load the data into an Excel spreadsheet. This was possible until Excel 2013 (maybe in 2016 as well). In this article I show you, how DAX Query Tables can be created in Excel 2019.

If you want to read more about Power BI, subscribe to our newsletter.


Solution until Excel 2013, maybe 2016 as well

In several blog articles the possibility was described, how data from the Power Pivot model can be materialized in Excel.

In all these blog articles either a table was created using VBA scripts (Chris Webb – https://blog.crossjoin.co.uk/2013/02/15/dynamic-dax-query-tables-in-excel-2013/),
the connection string in the .odc file was adapted (
Matt Allington – https://exceleratorbi.com.au/dax-query-tables-in-excel-2010/ and Marco Russo – https://www.sqlbi.com/articles/import-data-from-tabular-model-in-excel-using-a-dax-query/)
or the connection to the data model was adapted in Excel (Kasper de Jonge – https://www.powerpivotblog.nl/implementing-histograms-in-excel-2013-using-dax-query-tables-and-powerpivot/).

While the paths via the .odc file and VBA scripts are still possible, in Excel 2019 it seems that it is no longer possible to adapt the data model connection directly. The query properties are all greyed out and cannot be customized.

This way was unfortunately obstructed with Excel 2019.

Query Properties
Query Properties

Solution in Excel 2019

Fortunately, there is another way, which also works in Excel 2019. This is not intuitive, but at the latest comprehensible after the first use.

The solution is – Power Query!

First, we import the data of a Power Query query not only into the data model as usual, but also into an Excel table.

Import Data from Power Query
Import Data from Power Query

Actually, we only need the data in the data model and we will remove it from the Excel spreadsheet later. For the time being we have to leave the data as it is, otherwise the solution won’t work.

Power Query Excel Tabelle
Power Query Excel Tabelle

Copying the table

The next step is to copy the table and paste it to another location.
This step is necessary otherwise the query will disappear when the import mode is adjusted.

Only now do we no longer need the first table and we can use the option “Only Create Connection” instead of “Table” in the import dialog. We get an warning message, but we can ignore it.

create connection only
create connection only
warning message
warning message

While the first table now disappears, the second, the copied, table remains.

Adjust DAX Query

How can the query now be adjusted so that we get results from the data model and not from the Power Query query? To do this, right-click on the second table and select “Table”.

Context menu "Tables"
Context menu “Tables”

And then “Edit DAX”.

C:\Users\hpfi\Desktop\Dialog Box Edit DAX.jpg
Dialog Box Edit DAX

An editor will appear, which will allow us to create our own DAX query.

Edit DAX
Edit DAX

We now change the command type from “Table” to “DAX”.

The first thing we do is to test a simple DAX command:
EVALUATE Date.

The table Date is the second query in our example data model.

EVALUATE tells Excel to import the entire table, which in SQL is a SELECT * statement.

Evaluate Date
Evaluate Date

Click on “OK” and the table will be updated and the whole table “Date” will appear.

Excel Table "Date"
Excel Table “Date”

All Freedoms with DAX Queries Tables in Excel 2019

From here we have all the possibilities that DAX offers us.

For example, we can create a DAX Query that delivers the turnover per year, month and customer.

To do this, we enter the following DAX query in the open editor:

DAX Query Statement
DAX Query Statement

And the result is an Excel table with exactly these values. The values of the previous “EVALUATE Date” query disappear and are replaced by the values of our DAX query.

DAX Query Tables in Excel 2019
DAX Query Tables in Excel 2019

Building on this, further evaluations or diagrams, for example, can now be created.

Conclusion

Although Excel 2019 does not offer all possibilities to customize the connection string, DAX queries can still be created and the results displayed in Excel 2019 using Power Query.

What other methods do you know to display DAX query results in Excel?

Share your knowledge with us and leave a comment.

You can read more about Power BI here.

Happy Querying!

If you want to read more about Power BI, subscribe to our newsletter.


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: