skip to Main Content

by Matt Allington

I have learnt a lot over the last year working full time as a Power Pivot professional.  And some of the things that I dismissed as “not useful” very early on have turned out to be very useful indeed.  The second option in the Table Import Wizard is a case in point.

option 2

Very early on I dismissed this as being “not useful” for Excel users of Power Pivot (although I could clearly see the benefit for SQL professionals that could already write SQL code).  But I have to say I was wrong about this and I think there is a lot of value in this second option for Excel users.

I was re-reading the excellent book by The Italians over the weekend.  I find that reading quality books for a second and third time is a worthwhile and rewarding pursuit.  Often things that didn’t mean a lot the first time you read will have a new meaning and relevance the second or third time around.  Anyway when I got to the part about importing data from a database, there in front of me was reference to the “Design Button” which appears on the “less preferred option 2” import screen.

designbutton

I have been using this less preferred option 2 screen for some time now (read about other tricks I use here) but I have never noticed this button before.

So how do I use this button?

This button is very useful when you want to import data from a database but you don’t have access to an IT buddy to help you out by writing the SQL code you need. Now it is not the greatest user interface tool that you will ever encounter, but it is very do-able for the average Excel user. 

Let’s consider the following scenario using Adventure Works reporting DB for SQL Server.  In this example, I don’t want to bring in the full low level transactional data from the source database but instead I want to bring in a summary table for a defined period of time.

eg  Give me a table that summarises sales by day and by territory, and also how many invoices there were for the year (say 2003).  So I really want a table something like this to put into Power Pivot.

summary

Here are the wizard driven steps to complete this task.

Go through the normal “Import from Database” steps as you normally would for Power Pivot.  Select your server name and database name in the wizard.

image

Select the previously unloved option 2

option 2

Give your query a name (this will be your table name inside Power Pivot) and then click the Design button.

image

Expand the table navigation pane on the left and select the columns of data needed.  In this case we want order date, Territory Key, Order Number and Extended Amount.

image

In the selected fields section (top right), click the “Group and Aggregate” button and then select the “group by” actions needed for each field.  We want to group by order date and territory key, then do a distinct count on order number and sum up the extended amount.  These are not difficult concepts for Excel users to grasp even if you don’t know database design or the SQL language.  “Group By” simply means “give me a sub total at this level”.

image

Finally you apply a couple of filters to limit the results to the calendar year 2003.

image

After you click OK, the Design Wizard returns the appropriately written SQL code that will be used to fetch the data you need from the database.

image

And this SQL code is not hard to read or understand.  Once again, well within reach for the average Excel user that is learning Power Pivot.  In fact you can even change the SQL code here (to change the returned column names as an example).

Change this part

SELECT
fctSales.OrderDate
,fctSales.SalesTerritoryKey
,COUNT(DISTINCT fctSales.SalesOrderNumber) AS [Count Distinct_SalesOrderNumber] ,SUM(fctSales.ExtendedAmount) AS Sum_ExtendedAmount

to this

SELECT
fctSales.OrderDate
,fctSales.SalesTerritoryKey
,COUNT(DISTINCT fctSales.SalesOrderNumber) AS [Total Orders]
,SUM(fctSales.ExtendedAmount) AS [Total Sales]

 

Then click Finish and finalise the import of your summary table.

From here you can go ahead and bring in more tables of data, or start working on your data model as required.

Personally I think any Excel user that is regularly importing data from a database should invest a small amount of time learning how to read and write the SQL language.  12 months ago I classed my SQL skill as “knows what it looks like, can read it, but not good at writing it”. 12 months later having used SQL quite a bit for Power Pivot data imports, I class myself as “competent”.  So if I can do it, you can do it too. And UI tools like the one above can help you get started, and you will be writing your own SQL code without help before you know it.  Then there will be no stopping you.

Matt Allington is full time Self Service BI professional based in Sydney Australia

Matt Allington

Matt Allington is a Microsoft MVP specalising in Power Pivot, Power BI and Power Query Consulting and Training based in Sydney Australia. Visit Matt's blog here.

This Post Has 25 Comments
  1. Hi Matt,

    Thanks for this article.

    The source of our Excel Power Pivot data model is an Oracle Database. In the course of time our model was getting more complex with regard to the number of measures, calculated columns, relations and pivot tables.

    So, when I add a new measure or edit an existing one, I am getting, after waiting a long time, Excel recommendations to switch to a 64bit Version or messages like ‘Not enough Memory…’, but I have a well-equipped workstation with a lot of memory.

    The idea now is to shift some calculated fields into the SQL statement in the wizard (in order to reduce the computing power). This works in general for some calculated columns which contain basic operations (+,- etc.) or formulas like concatenate, but if a little bit of logic is involved I guess I need more than standard SQL(?).

    Do you know if the wizard ‘supports’ PLSQL?

    Before, I have tried a Power Query solution and PQ could calculate all columns I need, but then I realized the PQ & PP data model issue when there is a change in PQ or in PP which effects the connection, which is a great pity. The offered workarounds for this issue don’t work in our case, for whatever reason.

    Cheers
    Gökhan

    1. If you are using 32bit, you are limited to 2GB of ram regardless of how much memory you have. Rob blogged about a “hack” here that may help without upgrading to 64 bit. https://powerpivotpro.com/2015/05/grab-bag-of-fun/. If you do it, please post back your findings.

      I can’t see how moving calc fields into SQL will make any difference. Unless you actually mean only calc columns. If the latter, then yes you could do this, but first you should check if you can move the calc columns into calc fields. Read my blog post about this here https://powerpivotpro.com/2014/10/5-common-mistakes-made-by-self-taught-dax-students/

      The PQ issue is explained here and can be avoided. But it may be too late if you already made the error.

      http://exceleratorbi.com.au/solved-power-query-issues-with-power-pivot-exception-from-hresult-0x80020009/

      My best guess is you have too many (ie any number > 0 ) calc columns. Sometimes these are needed, but not that often.

  2. If you copy the connection string from your changed power query into powerpivot, it doesn’t work? If column names are changing, try deleting those out of the powerpivot model and copying in the new connection string

  3. @Matt

    1) The Import Dialog of PP itself is “Unloved” – With PowerQuery Freely available in 10 and 13 and now fully integrated in 16 – Who needs the rather restrictive dialog of PP to import Data ?

    2) If you are going to pull data from a SQL Server or any DB for that matter – you are better off writing those SQL Statements in a “View” (or Query) and store it in the Database and then Pull the data from the View rather than the Table

    With this you get the advantage of “Query Folding” when using PowerQuery

    1. 1) I often bring a table into Power Pivot using the standard table import. I then write lots of measures. Some time later I copy the workbook and then reimport a sub set of data for a different purpose. In this use case, switching to Power Query (when your table is already full of measures) is not a good option.

      2) writing a view or query in SSMS or equivalent is fine for people who know SQL and have access to SSMS. It is not going to work for a lot of Excel users who don’t have access to the db.

      There is no 1 right way to do any of this. I am just highlighting another option that has merit over the standard default option in some use cases.

      1. @ Matt
        If you are referring to the connection in PP breaking once you change something in PQ this is only a issue in 2010 (for which there is a work around of copy pasting the new Connection string)

        They have fixed this has been fixed in 2013/2016

        Also the safest way of defining measures is against a dummy “Measures table” rather than on the actual fact table itself

        This lets you delete and re-import the fact table without disturbing your measures

        1. No, this issue is not fixed in 2013/2016. And what’s worse, you can’t copy and paste the connection string in 2013. So simply don’t make any changes to a table loaded from Power Query inside Power Pivot and there won’t be a problem.

          1. You dont need to
            Just Uncheck Add to Data model, Make Changes, Check it back again
            This way you can change the data sources, add columns etc
            The only thing you may have to build are the relations – but not the measures – because they would all be defined on a stand alone disconnected table
            Let me know if you want me to email you working example of this.

  4. The “ODBC Tables” database option in Power BI Designer (coming soon to Power Query?) builds the query in the background using PQ filters. If foreign keys are defined, you can join to other tables. Pretty slick, but you won’t actually see SQL unless you do a trace.

  5. hi Matt

    Do you have any experience using a parameter in a field??

    i am working on it , but without success

    if you have a example, I’d appreciate you

    thanks for sharing

        1. Hi Roberto,
          with Power Query you could actually dynamically reduce the amount of data that will be loaded to your PP model:

          There you can pass values that sit in cells of your Excel-sheet to PQ queries where they can be used to filter or manipulate your tables.

          Having in mind the contstraints of the Interface between these tools – just check if it’s worth for you.

  6. This is actually the way PowerPivot made me learn SQL.

    With Excel, I used MSqery to create queries accessing a cache db via odbc, but with PP that was not possible any more.

    So at first, I just copied the queries and did all the calculating with Powerpivot, but over time the queries got more and more complex, with calculations and transformation of columns being pushed upstream, making the datamodels lighter and faster.

  7. Hi Matt, I am using MS Excel 2016 Pro Plus and when I click Design, Table Import Wizard is showing a window which doesn’t have any of the options mentioned in the tutorial. Rather it shows a plain window. Kindly assist in how can I get hold of the right options.

    1. I haven’t used 2016 (too many customers still use 2010/2013). Based on your description, it sounds like it is more like Power BI Desktop than Excel 2013. Assuming you are trying to import from SQL Server, try to use Get & Transform (Power Query), import from SQL Server and add an optional TSQL Statement when prompted

  8. I agree with Rahul – the option has disappeared in 2016.

    I’d previously used it in PowerPivot 2013 and 2010.

    I suspect MS are trying to push people into using another product to do this…
    It’s a bit of a downgrade of PowerPivot for 2016
    – I’m no longer able to consolidate two tables (same structure) into one as I’d intended.

  9. Hi Matt, I am using MS Excel 2016 Pro Plus and when I click Design, Table Import Wizard is showing a window which doesn’t have any of the options mentioned in the tutorial. Rather it shows a plain window. Kindly assist in how can I get hold of the right options.

    I agree with Rahul – the option has disappeared in 2016.

    – – – – – – – –

    I also initially could not find Table Import Wizard – Query Designer. My klunky solution was to
    1) export each desired table from the ‘source’ database to a txt file
    2) import each desired table (the .txt files) into an Access database
    3) From Data Model window, select Home > Get External Data > From Other Sources > Microsoft Access > connect to Access database > Next > select ‘Write a query that will specify the data to import’

    This is a round-about solution but it gets the job done and lets me use the Query Designer.

    Have not tried using a SQL database instead of Microsoft Access.

  10. Matt, thank you for the quick response.

    Question: I am using Excel 2016 (Microsoft Office 365 ProPlus). I would like to import three .txt files, link together and apply filters to produce an output.

    What is best method for this? I know how to link tables using primary keys but how best to filter a result within the data model? Should I filter in the PQ window? Could you provide more detail on your comment that ‘Power Query has superseded the old approach’?

    Any assistance is appreciated.

    Doug

    1. In the famous words of Marco Russo, it depends. if the three .txt files are identical in structure, then use my approach covered here https://exceleratorbi.com.au/combine-csv-files-power-query/ If they are all different shape, then you should connect to each one in Power Query, one at a time, standardise the table shape and then append them together.

      Power Pivot is 3 years older than Power Query. It was not until Excel 2016 that Power Pivot and Power Query were both fully incorporated natively inside Excel. Prior to that, Power Pivot had to have its own data load tool. This is when you “get external data” from within power pivot. Power Query is 100x better than this experience. You should go to Power Query in Excel, create your loads and then select “Load to Data Model” to load it into Power Pivot.

  11. Matt,

    You explained it perfectly.

    I use Power Query extensively and load directly to the Data Model. Just thought I was missing a ‘new’ feature with the Query Designer. In the new Excel 2016 world, Power Pivot is the query designer.

    Question: does the Data Model provide a method to see the equivalent SQL (not ‘M’) code for all the joins and filters used to create the data model or Power Pivot output? Or is there a tool available for this? I use Excel (PQ, PP, Data Model) for development but need to transfer my Data Model to developers for use with Oracle.

    Thank you for being such a great mentor.

    1. Data Model = the Vertipaq engine in Power Pivot. There is no SQL involved in the data model. But you said “equivalent SQL”. You can use DAX Studio to connect to the data model. You can’t see the static “equivalent SQL” but when you write a query in DAX studio, the equivalent SQL for the query is displayed inside DAX Studio.

      If you use Power Query to load from SQL, you can use Query Folding to see the actual SQL. See my blog post here.https://exceleratorbi.com.au/how-query-folding-works/

  12. Hi Matt,

    The PowerPivot workbook is reading a MSSQL server database using the SQL option
    Many of the columns are calculated (CASE, SUBSTR, TO_CHAR, IF, ect…),
    also I am using Unions and Joins.

    I had to edit the query in power pivot to add a member in the filter using query editor . After editing when i save the query , its throwing this error .
    power pivot –> manage –> design tab –> table properties –> edit the query ( save )

    The SQL statement is valid, but I am getting the Capacity error.
    ” Capacity exceeds maximum capacity. Parameter name: capacity ”

    The same SQL query works fine in a different application.
    Why is not working in Power Pivot?

    Any recommendations are more than welcome? Thanks for your help.

Leave a Comment or Question