skip to Main Content

-Posted by Rob Collie

OK, ignore my last post, that was just silly.

Yesterday one of my fine colleagues at Pivtostream mentioned that he was seeing both field lists simultaneously.  Then followed an exchange where I said “BS,” he said “bet me some cash, oh PowerPivot boy” and, well, he was right.

One should be careful never to wager against one’s own boss.

Anyway, in beta releases of the addin, it was IMPOSSIBLE to bring up the Excel field list without disabling the addin.  Apparently, the team decided at the last minute that allowing the user to bring up the Excel field list could compensate for the lack of certain features.

It’s a brilliant move on their part.  So here it is, both field lists, living together in harmony:

Built in Field List and PowerPivot Field List

You don’t have to disable anything.  Just go to the Options tab on the ribbon when you have a pivot selected, and click the field list button there:

Excel Field List Button

Implications Galore!

OK, so…  you can do the defer layout update trick which is excellent for long-running queries, without turning off the addin, that’s huge.

But wait, there’s more 🙂

Can Move Measures to Rows or Up and Down Relative to Other FieldsYou can also adjust the layout of your measures.  Try dragging the little Sigma “Values” capsule around the Excel field list (shown here at right) and you will see what I mean – move it to Row Labels for instance.

This also comes in handy when you have other fields on Column Labels and want to change whether Measures are nested inside of those fields or vice versa.

But the last trick that this side by side field list stuff enables is truly my favorite…

 

 

Batch Creation of Measures Without Refresh!

Follow along, this is fantastic.

Step one:  turn on defer layout update:

Defer Layout Update

Two, go create a measure:

Create New Measure Without Refresh

Three, ignore this error you get when you enter the formula and click OK:

Ignore This Formula Is Invalid Error

Four, observe that the measure was created anyway:

Measure Was Created Anyway With No Pivot Refresh

This changes my life in rather significant ways.

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 11 Comments
  1. @Rob… Noticed something about a power pivot built on a Excel Data source.
    If there is a column called say “Date” that has dates, the Pivot table will not let you group it by Months/Years etc….Is this is Bug ?

    1. Not a bug, but definitely a common complaint.

      Doesn’t matter where the data came from – SQL, Excel tables, text files, whatever – once it’s loaded into PowerPivot, you are using the Analysis Service engine as your pivot engine, not the Excel pivot engine.

      And the AS pivot engine does not support that feature, at least not today.

  2. @Rob.
    Also noticed you do not get the option to build SQL query on an Excel Database that you get on Access or SQL server…..Bug ???

    1. I suspect it is not a bug. In many ways, querying an Excel file is just like querying a text file. There is no db engine in place to process the query. Now if you see something you can do with a text file query that you cannot with an Excel file, then I say that’s a problem.

      If you load your Excel file into Access or SQL first, then you get the benefit of the db engine processing queries for you.

      Of course, in many cases, that is not a practical option.

      1. Rob,
        We can build a query on an Excel Data source in a Normal Pivot.
        So lets say you have a file with 3 sheets(tables) with columns(fields) Customer, Country Sales Year – and each sheet having data for a particular year
        Then the below code works fine in a normal pivot…. cant imagine why they cant replicate it for a Power pivot
        SELECT *
        FROM `D:Pivot_Mult.xlsx`.`’2004$’` `’2004$’`
        UNION ALL
        SELECT*
        FROM `D:Pivot_Mult.xlsx`.`’2005$’` `’2005$’`
        UNION ALL
        SELECT*
        FROM `D:Pivot_Mult.xlsx`.`’2006$’` `’2006

    1. Welcome back Mr. Excel, from your travels far and wide!

      Yes, it appears that I may be the only blind man here, blinded by my knowledge of how we designed it while I was still in Redmond 🙂

Leave a Comment or Question