DAX Query Tables in Excel 2010

I was helping a participant at http://powerpivotforum.com.au with a problem. Long story short, she needed to return a table to Excel from a data model – not a Pivot Table. This concept can be a bit strange at first – let me explain.

Tables – Not Pivot Tables

A Pivot Table is a tool that aggregates data and allows you to slice and dice to get different totals and sub totals of that data. This is great when your tables are full of numbers and you want to aggregate those numbers. But consider the following scenario. What if you want to return a table that lists out all the names and addresses of customers that have purchased more than $2,000 in products from your company? Well you could set up a pivot table like this.

pivot table option

In the above Pivot Table, there are 5 different columns from the Customer Table and 1 measure (there are actually many more text columns that I want to extract other than those shown here). The columns of data from the Customer Table have been placed in the rows section of the Pivot Table. The more columns you put on rows in your Pivot Table, the slower the performance will be. What is worse, if the columns come from different tables, you can get an exponential slowdown in performance. Read about why that is here.

OK, so the answer to this problem is don’t use a Pivot Table, but use an Excel Table instead. There are few articles on the web that explain how to do this in Excel 2013 including this one from Scott at TinyLizard.com. I may write my own post about that another time, but today is simply to explain how to do this in Excel 2010 as the process is not the same.

When ever I want to learn something about DAX Queries, I always head over to http://sqlbi.com. A good way to search a specific site is to use the Google SITE: keyword in your search as shown below.

google

The second item in the list looked promising, so I took a quick read. It refers to inserting tables from a Tabular model, but I thought I could modify the technique for Excel 2010. I did that and it worked – here is the full process.

I have a sample workbook setup using Adventure Works. Here is the data model (shown below) and you can download the workbook here (Excel 2010).

datamodel

  1. Click in a blank cell in your workbook
  2. Select the Data Menu
  3. Click Existing Connections
  4. Select PowerPivot Data
  5. Click Open
    data connections
  6. Select Only Create Connection
  7. Click Properties.
    import data
  8. Click on the Definition Tab
  9. Select Export Connection File
    connection
  10. Give your connection a name
  11. Click Save
    save
  12. Open Windows Explorer and browse to the Data Sources folder
  13. Right click on your new connection
  14. Select Edit in NotePad
    data sources
  15. Locate the section of the XML shown in the image below
  16. Replace the word Cube with Query
  17. Replace the word Model with any valid DAX Query statement. The easiest is simply EVALUATE AnyTableName. I have used EVALUATE CUSTOMERS
    So I started with this
    edit
    And ended up with this
    edit 2
  18. Save and close Notepad.
  19. Finally, go back to Excel and select a blank cell
  20. Navigate to the Data menu
  21. Select Existing Connections
  22. Select your new connection (from step 10)
  23. Click Open
    final
  24. You will see the same dialog as in step 4, but this time you have an option to insert a table. Select Table from the list
  25. Click OK.
    insert

And there you have it – a new table in your workbook that is sourced and linked to your data model.

table

Returning this text detail in a pivot table would not be a pleasant experience.

Now this table is the entire Customers table, and what I really wanted was a list of customers that have purchased > $2,000. To fix that, you will need to write some DAX Query code.

  1. Right click anywhere in the table
  2. Select Table
  3. Select Edit Query

edit query 2

You have the opportunity to replace the query you added in step 17 with any valid DAX Query. I am not going to go into DAX as a Query Language here as that is a topic for another day. To finish off this process, I have added the following DAX Query

And my table updates with the full list of customers that have sales > $2,000

final list

For more information about writing DAX Queries, I recommend you take a look at this article at SQLBI.com. It is not specifically an intro to DAX queries, but I found it very helpful in learning what can be done.

A Final Word

I am not sure what will happen when others open this workbook. It is unclear to me if the new connection I created is automatically embedded in Excel or if it is still sitting on my PC. I would be interested to hear from any Excel 2010 user that opens the sample workbook to find out if the connection can be edited by them.

Comments

  1. Great way for getting a table and easier than writing a bunch of text measures to make a more responsive pivot table.

  2. @Matt
    In Excel 2010 – you just need to do the following
    1. Create a Pivot
    2. Double click on a number – This will create a table
    3. Right Click Table->Edit Query
    4. Modify the DAX Statements

    • It does work… as insulting-ly simple as it is, given the number of hours that I put in trying to do exactly that !

      Also, given that this mothod does not create any “local” External Connection file, the fact that other people use it on their PC should not be a problem.

Leave a Reply

Your email address will not be published. Required fields are marked *

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x