-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.