Archive for DAX Queries

Extract Tabular Data From Power BI Service to Excel

Someone asked me a question yesterday about exporting data from the Power BI Service into Excel.  There are a few options to do this however they all have their problems (these problems are not covered in great detail in this post).

  • Power BI has an inbuilt export data feature (there is an export limit of 30k rows for a free account and 150k rows for a Pro account.  Plus the connection cannot be refreshed).
  • Analyze in Excel (can only view the data in Excel via a pivot table, not a table of records).
  • Download the Power BI Desktop file to your PC and use DAX Studio (you have to download the entire model just to get the records you want, besides it may not even work at all).

I started to think that I may be able to connect directly to Power BI Service using DAX Studio and then use the techniques I cover here to extract the records.  I tried this but couldn’t get it to work (please let me know if you find a way to do this).  I started searching and found this old article I wrote in 2015 that uses a technique I learnt from Marco Russo.  l have used a similar approach to create a direct query of a Power BI Data Model and extract the records directly into a Table in Excel.

Edit: 11 Jan 2017 – see the bottom of this post to see how Chris Webb recommends to do this task – it is much easier :-).  But read on for some interesting learning.

Create a Link to PowerBI.com

The first thing I did was to select my dataset from the Power BI Service, clicked the Ellipsis (1 below) and then Analyze in Excel (2 below).

image

This downloads an ODC file to the downloads folder.  Mine was called Adventure Works.odc

image

Note: there have been many updates to the Analyze in Excel connectors since its launch last year.  If you haven’t updated the Analyze in Excel connectors for a while you would be well advised to do that first before attempting the rest of the procedure that follows.  This can be done by logging into Power BI, clicking Downloads (shown as 1 below) and then downloading and installing the connector software (2 below).

image

Edit the ODC File to Make it a Query

I then edited the ODC file using Notepad.  It looks like this originally

image

It is a bit hard to read the file as is, so I cut and paste the contents into an HTML editor to improve the readability, (as shown below) then pasted the formatted text back into Notepad.

image

As you can see above, the default command is “Cube” and the default text is “Model”.  The trick is to change the command to be “Query” and replace the text to be any valid DAX Query.

I changed lines 19 and 20 below as follows:

image

<odc:CommandType>Query</odc:CommandType>
<odc:CommandText>Evaluate Sales</odc:CommandText>

The Command Text I used above is one of the most simple DAX Queries you can write.  All queries must start with the EVALUATE statement followed by any valid DAX code that returns a table of data.  The simplest way to do that is just specify an existing table (Sales in this case). If you are doing this, you can use any table name in your data model.

I then saved and closed the ODC file.

Open the ODC in Excel

To open the ODC file in Excel, I then simply double clicked on the file.  This opened Excel and gave me the following security warning

image

I clicked enable, and there appeared the entire Sales table from my Power BI data model directly in a table in Excel.

image

Now that the connection has been established, the file can be saved and refreshed as needed in the future.  To refresh, simply open the file, right click on the table and click “refresh”.

Change the Query

So far this is pretty boring albeit maybe useful for some people.  Much more interesting is to be able to extract a subset of the data to Excel.  To do this you need to learn a bit of the DAX Query Language.  I am not going to go into the DAX Query Language in detail in this post, but below I show how to edit the current query and change it to a new query – in this case a query that will return the Total Sales by Invoice Number.

To edit the existing query, I simply right clicked anywhere in the table (1 below), then selected Table (2 below), then Edit Query (3 below).

image

This brings up a dialog that can be edited as needed.

image

For my demo, I replaced the command text with my new DAX Query as follows

image

After clicking OK, this query then returned a table of all invoice numbers and the total value of sales for those orders.  Interestingly the number formatting for Total Sales did not flow through to Excel as I expected it to.

image

Chris Webb’s Trick

Edit 11 Jan 2017. Chris Webb tweeted an alternate approach that is faster to execute. Simply create any pivot table using Analyze in Excel, drill through to the underlying table and then edit the query as before.

Further Learning

I may come back and blog about DAX as a Query language at another time.  Until then if you would like to learn what is possible, I recommend this video from Alberto Ferrari (although it is a bit advanced unless you have some solid understanding of tables and databases).

www.sqlbi.com/tv/querying-in-dax-tee2014/

Quadruple Nested SUMX or CROSSJOIN

Level: Intermediate

I had an interesting problem with a client last week where I built a data model to calculate rebates payable to customers based on their sales turnover.  There were a few challenges that I am going to cover below, including:

  1. There needs to be a user friendly way to capture the rebate data, but it then needs to be transformed so it can be used in the model.
  2. There is a nested SUMX problem – quadruple nesting in this case.  I solve this with a CROSSJOIN (and then SUMMARIZE) that I will explain below.
  3. There is also a quadruple nested IF(HASONEVALUE()) problem that I solve with an innovative formula that I will also cover below.

The Business Scenario

The scenario is a wholesale business that sells products to customers.  Depending on the contract with the customer, a rebate may be paid on parts of the sale.  The objective is to create a tool that will correctly calculate the rebate payable at all aggregation levels in a pivot table. Read More

Getting Started with DAX Studio

Level: Beginners

DAX Studio is a fabulous free tool that allows you to directly query your Power Pivot/Power BI data models.  It is not immediately obvious (particularly to beginners) exactly how using DAX Studio can add value.  I often refer people on various forums to use DAX Studio but then lack a suitable reference to refer them to so they can get started.  This blog post today is a simple reference to help anyone that uses Power Pivot get started with DAX Studio.

But What Can I Do With DAX Studio?

I’m glad you asked – lots of things – much more than I will cover here today.  But here are a few things that you can do that are really useful. Read More

DAX Query Tables in Excel 2010

I was helping a participant at 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.

Read More