The Ken Puls (Excelguru) Blog

When I was down at Microsoft for the MVP Summit, I suggested that, as the “other most powerful part of Excel” that PowerPivot, like the Visual Basic Editor, should be given it’s own keyboard shortcut.  Since Alt + F12 is currently empty, and since it’s right next to the VBE’s Alt + F11 shortcut, it only seems logical that Alt + F12 would make a logical choice.

Well, I got tired of waiting… not that I ever expected they’d release a patch for that or anything.  So I up a macro to do it.  It’s pretty short, works with Excel 2010 and 2013, and can be stored in the Personal Macro Workbook.*

If you know your macros, or you already have code in your personal workbook, then add the Workbook_Open line and the Sub OpenPowerPivot to your project.  But if you’ve never written a macro, or you’ve never used the personal macro workbook, here’s how you do it:

• Expose the Developer tab
• Click on “Record New Macro”
• Choose to store it in the Personal Macro Workbook.  (That will create your personal macro workbook.)
• Stop recording
• Press Alt + F11
• Find VBAProject(Personal.xlsb) in the Project Explorer.  (The project explorer is the treeview on the left.  If it’s not showing, press CTRL+R to display it.)
• Double click the ThisWorkbook module inside it
• Paste the following code in the code pane:

Private Sub Workbook_Open()
Application.OnKey “%{F12}”, “OpenPowerPivot”
End Sub

• Now open the Modules folder and double click Module 1
• Replace the code in there with this:

Sub OpenPowerPivot()
On Error Resume Next

Select Case Val(Application.Version)
Case Is = 14
Application.SendKeys “%GY2″
Case Is > 14
Application.SendKeys “%BM”
End Select

On Error GoTo 0
End Sub

• Close the Visual Basic Editor
• Close Excel and say Yes when prompted to save changes to the Personal Macro Workbook
• Re-open Excel and Press Alt+F12

Isn’t that just the height of lazy?  (I mean efficient!) 

*Just a quick note here… if you float back and forth between Excel 2010 and Excel 2013 on the same machine, Excel locks the personal macro workbook for editing when you open the first Excel instance.  So if you open a new copy of Excel, you’ll get a prompt.  Just say okay though, and it will still work fine.

Just a quick reminder if you’ve been sitting on the fence… Chandoo’s PowerPivot online course registration closes in just over 12 hours (midnight Pacific Time, 2013-02-15).

Don’t get left behind… Click the image below to sign up!

I don’t think it’s a secret to anyone who reads this blog that I’m a big fan of PowerPivot.  It’s got some huge capabilities, and I’m convinced that it will be a game changer in the BI landscape.

The challenge though, is that PowerPivot isn’t just a “pick it up, it’s easy” kind of thing.  Take it from an Excel pro, you’re going to need some help to get up to speed with it and really make it sing.  Well good news… Chandoo is releasing the first online PowerPivot course, and registration starts now!

Here’s the details of what Chandoo is going to cover:

What is in this course:
Power Pivot, an Excel add-in makes it easy to connect, analyze & visualize massive amounts of data. This course aims to teach you how to use Power Pivot to analyze data, create advanced reports & prepare dashboards all from the familiar Excel interface. This is ideal for data analysts, reporting & MIS professionals, business analysts, managers & dashboard makers.

Who should sign up for this course?
This course is ideal for data analysts, reporting & MIS professionals, business analysts, managers & dashboard makers.
Please note that you should be familiar with Excel & Pivot Tables and running at least Excel 2010 to enjoy this course.

Important Dates:
- Course registration opens on – 6 Feb 2013
- Registration closes on – 15 Feb 2013
- Classes begin on – 18 Feb 2013
- Classes end on – 31 May 2013
- Online access is valid until – 18 August 2013

The total cost is $247, and you also get a bonus copy of Rob’s DAX eBook (which you’re going to need).

Chandoo is no stranger to online training, having run his online Excel School for some time now.  But what makes this one even better is that, not only does it cover all the awesome content you need to get up to speed, it also includes a couple of guest lectures by some pretty cool people.  One is Rob Collie of www.powerpivotpro.com and the other is… me!  (I’m really looking forward to being a part of this!)

So what are you waiting for?  Click the image below to sign up!

As many readers of this blog will know, I am a huge fan of PowerPivot, and honestly believe that this is THE most important feature to hit Excel since VBA was introduced to the product. And anyone who has ever taken a course from me knows that VBA is the greatest thing to be added to Excel since the grid was mapped into rows and columns.

For those who don’t know, PowerPivot was a free add-in to Microsoft Excel 2010, and is now baked into the Excel 2013 release. It basically allows us to pull data from different sources (multiple databases, text files, web data feeds, excel worksheets and more) into a separate layer of our file, and create relationships between the resulting tables. Basically you can aggregate data from a bunch of different sources and EASILY aggregate it into your very own business intelligence engine.

The amazingly cool part is that it’s not hard to do this. With a little knowledge about relational data (and I mean a little), and knowledge of text functions like these, you can build the columns necessary to relate your data. From there you create the table relationship (PowerPivot even tells you if you try to build it backwards), and you’re off to the races.

The results of this are, quite frankly, groundbreaking. You can then build PivotTables off this data: PivotTables that are sourced from multiple tables at once. No more massive VLOOKUP tables to build one huge data table with everything. It’s simply no longer necessary. And with the variety of sources, you can even pull in and start adding items to your pivots that you never thought of before, like the weather for example. Does your corporate database have that? I doubt it, but who cares? Just source a weather feed from the internet, relate the dates to the dates in your sales table and presto! You can now see how sales were on the sunny days in a month vs the cloudy ones.

It’s amazingly easy to get some cool stuff out of PowerPivot… at least… to a point. To over-simplify things, I’d say that PowerPivot allows you to do four overall things:

1. You can source and relate tables to build your own aggregated data source (a mini BI database if you like.)
2. You can build PivotTables pulling in fields from multiple tables.
3. You can build OLAP formulas, allowing you to pull data for a specific element right into the worksheet, without building a whole PivotTable to do it.
4. You can build super duper wickedly complex and powerful calculated fields for your PivotTables using DAX.

To me, the first three were easy. As an Excel pro with a bit of relational database knowledge, I’ve been able to work through the first 3 over the past couple of years with very little in the way of roadblocks. And by doing so, I’ve built some amazingly cool things for our company. Yet despite this, I’ve always been aware that the real piece de resistance, the part that really makes things sing in PowerPivot is DAX.

What the heck is DAX? It stands for Data Analysis eXpressions, and is the formula language that you use to create what PivotTable users know as “Calculated Fields”. It is unbelievably powerful but, without tutelage, it is unbelievably hard. For almost two years I’ve been struggling to understand DAX.

That ended this past week.

Last week I bought a bought Rob Collie’s new book “DAX Formulas for PowerPivot: The Excel Pro’s Guide to Mastering DAX”. When you are ready to take the DAX journey, you MUST have this book.
Rob’s writing style is identical to that you’ll find on his blog at http://www.powerpivotpro.com/. In fact, the only difference is that he doesn’t fill up any real estate with movie quotes. What you do get is a huge amount of insightful information.

I can honestly say that, despite being a fairly accomplished Excel pro, I have been unable to wrap my arms around DAX. But that is over. Four days of working through this book with my own data and I am now making some damn cool stuff that eluded me before.

What I love about this book is that Rob sat down and carefully thought through how an Excel pro should learn DAX, then started at the beginning. It progresses logically, is very easy to read, and actually gets you there. Yes, you need to practice, but weren’t you going to do that anyway? And when you practice…

There’s something really important between these pages that I doubt you’ll ever see in the advertising. With any programming language (yes, Excel formulas fit that bill, as do DAX formulas) there are two critical things to learn. The first is Syntax (how you do something), and the second is how to debug something. Rob gives you both, which is critical on the path to mastery. He has some nice little charts that explain how filters are applied to the CALCULATE function, (which is like SUMIF on steroids.) With those charts, you can actually follow the steps to figure out exactly why your measure is not doing what you want. (Trust me, it was doing this that actually helped me finally get that Eureka moment that I’ve been looking for!)

Four days. I’ve gone from not being able to make anything but the most simple DAX measure to the point where I can create some pretty cool CALCULATE functions (my new best friend), with nested date/time intelligent functions as well. I’ve been pursuing that for TWO YEARS!

In an hour this morning, I was able to build a nice little “Server Contest” dashboard for our food and beverage department. It has slicers to drive down into sales areas, major groups (entrees vs desserts), and dates. And it will correctly report the week-to-date, month-to-date and prior weekly sales in units, as at the selected point in time. (Previously, I was able to get the “effective date” to work, and my “current month” to work, but I could never figure out how to filter things to give me the correct month to date number if I clicked an earlier date. It always kept returning the real month to date, rather than the effective month to date.)

The dashboard is a beautiful thing (to us anyway), that we’ve never been able to produce until today:

After struggling with DAX for so long, I think it’s fair to say that I wouldn’t have been able to break through this without Rob’s help. I certainly wouldn’t have been able to build the report above in such a short time.

If you’re struggling with DAX, or just getting ready to start the journey get his book. You owe it to yourself.

You can pick it up from Amazon.ca (for all my Canadian colleagues) by clicking here. If you’d prefer to deal with Amazon.com, you can get it by clicking here. And/or from other routes as detailed on Rob’s Website.

I’ve been doing a lot of work with PowerPivot where I connect to databases. My normal development cycle is as follows:

• Connect to a view or table, pulling in ALL columns
• Work through my data scenario until I’ve worked out my logic and solve the issue I’m trying to solve
• Cut the table/view query down to just the required columns
• Add WHERE clauses to cut the data down to just the bare minimum I need
• Deploy to my users

By doing all this, I really focus on trying to optimize the file size and refresh time as much as possible.

As I do this, I cut the number of columns out, then flip from the GUI table view to SQL to add my WHERE clause(s). One thing that I find that really sucks though, is that when I do flip the “Table Properties” to SQL view, it comes out like this:

I would LOVE to see it come out more like this by default:

It would sure make it a LOT easier to read, and a LOT easier to work with.As it is, I now copy my code out of PowerPivot, then head over to Instant SQL Formatter.  Copy, paste, format, copy and paste it back into PowerPivot.  A heck of a lot easier to read, but also an unnecessary pain in the behind.

Hi everyone,

As per my last post, I recently set up a free public Excel Help Forum on my website. I’m pleased to say that over the past week and a bit we’ve attracted 50 new users, and now have over 40 threads with over 200 total posts on the site. Not bad for 10 days!

I’ve spent a lot of time configuring different options to try and make this forum as consumable as possible for people. In addition to the basic forum functionality, some of the modifications include:

• Serious anti-spam solutions to keep the board all about questions and answers, without having to wade through garbage
• Facebook integration, for those of you who like to tell your friends what you’re doing
• RSS feeds, for those who like to keep up to date on the topics, and drop in when you see one that interests you
• Tapatalk integration, for those who want to keep up with the site on a mobile device

And those are just the big ones. There’s been a lot of tweaks under the hood to make the experience as optimal as possible, and a few others are coming soon.

The latest thing I’ve worked on is adding what I hope to be THE Excel Events Calendar on the internet…

I’d like to invite everyone to use the Calendar on the forum as a public Excel Events Calendar. If you have, or know of, an Excel event in your area, please post it for the world to see. I’d like to make this the most comprehensive Excel training calendar on the internet.

There are only 2 rules I’d like to attach to this at this point:

• Put the location of the event in the title, if applicable. The goal is that people will be able to find events that they can attend in their own backyard, worldwide.
• Only the date(s) of event should be posted. (I don’t want a post each week reminding people.) If there is a registration cutoff date, please post it in the event information.

I think if everyone observes the above, the calendar should remain relevant and helpful to everyone.

Events I would expect to see include training courses (live or online), new book release dates, conferences that deal with subject matter relevant to the users on this site. (Be it BI, SQL, Sharepoint and more, so long as it has an Excel flavour, it counts.)

If you are a trainer, teacher, publisher, or whatever, I invite you to participate. Register for the site, if you’re not already, and post your event to the Excel Event Calendar. The more up-to-date and accurate we can make this, the more our community can rely on this being the source to come to for training courses. The more that happens, the more likelihood you’ll get signups. The more signups you get, the more likely you are to want to put on more training events, and the more the users win. It’s a self-fulfilling cycle that is in all of our best interests.

Oh, and one final word on this to everyone. I am not doing this to solicit commissions, affiliate links or funding in any way. There is already enough advert links on the site. This is about people helping people in our Excel world.

Hello everyone,

At the last MVP summit I had discussions with a couple of people about something that has been burning away in the back of my mind for quite some time… creating my own Excel forums.

I’ve been absent from the online forums for the last few years, and I’ve greatly missed it. With the amount of teaching I’ve done over the past few years I’ve constantly been referring people to other forums and blogs, and I’ve received several comments from those people asking about a forum on my own site. So during this time I’ve been musing it over, wondering if there is enough differentiation, or enough reason to take on the task of building and maintaining another forum out on the interweb.

Despite missing a few of my good friends at the last summit, it was one of the most exhilarating ones I’ve been too, for a variety of reasons. Some of it was technology based, of course, but there was some based on conversations with people that I never expected to have. The compounding of all of it together made me realize that I have been putting this off for too long, and that I actually needed to do it, to bring back the piece of me that I’ve put off for my job over the past few years.

To this end, I’ve begun the process of building another forum on the web, one that focuses on Excel and Excel users. I am hoping to grow it with the style of friendly community that was fostered at VBAExpress.com, but have the focus strictly on Excel users from start to finish, no matter the platform. There are forums there for Excel Client focused areas, Programming (VBA, .NET, XML, etc), PowerPivot and data needs, the recently released Excel WebApp and even Sharepoint. As I said, I want this to be a one stop portal for anything Excel related.

In addition to the Excel focused stuff, I’ve also added a simple section on the other Office apps. The reason for this is simple; every Excel user encounters the other Office apps, and runs into issues. I think it’s important that we have a place to ask/answer those too.

Oh, and naturally there is a chat forum, as no forum would be complete without it.

The forums are, of course, completely free to use. Simply sign up for an account, and post your questions. You’ll be notified immediately by email when someone replies, so you can jump right back to the forum and post further information if required, or see the solution that an expert has helped you build. You can even upload your own files to the forum so that people can see EXACTLY what you’re trying to do. After all, a picture saves 1000 words, right?

And if you’re an expert, or even if you’d just like to help, then post some answers! We love help, and encourage you to take part. Speaking from my own experience, I can tell you that there is no better way to better your own skillset than by trying to help others. You won’t always have perfect answers, but nor do we expect that. This is all about people helping people. The intentions are what is important.

You can find them at http://www.excelguru.ca/forums. (The registration button is in the top right of the screen.) Or, if you’d prefer, you can jump straight to the Registration link to create your account.

I look forward to seeing you in the forums!

I think this is kind of neat…

In a discussion about PowerPivot yesterday, one of my friends stated that it wasn’t really useful since you couldn’t perform writeback using PowerPivot. To him this is a very important piece in the Excel budgeting process. Now, I agree that PowerPivot doesn’t give you write-back to a database, but this got me thinking… we have linked tables, so why couldn’t we create a write-back loop for a model that was built entirely in Excel? Well, we can!

To be clear here, this only works if your entire model is built in Excel and PowerPivot. You can source data from elsewhere to supplement it, but the key is that the information will be written into the PowerPivot cube as the ultimate database. I am certainly not advising anyone to toss a database in favour of a PowerPivot file, but if you don’t have a database, and want to user PowerPivot as your DB, then this could work.

Here’s how I generated a writeback scenario…

To summarize this:

• The initial step is to create a table of data in Excel, format it as a Table.
• Next, we need to link the Excel table into PowerPivot. (Create Linked Table.) This action will upload the structure and data into PowerPivot, forming the beginning of the PowerPivot database.
• The next step is that we create a PivotTable in Excel, based off the data. At this point we will have a data table in Excel, and a PivotTable based off that data in another sheet. While I haven’t tested this, I don’t see any reason why you couldn’t avoid the PivotTable and just use cube functions based off the PowerPivot cube instead if you prefer. At any rate, here’s the PivotTable I set up:
• I then create a working sheet.
  • The purpose of this worksheet is to pull in the values from the PivotTable (or be populated with OLAP formulas), then allow the user to “override” the key sections. In my test, I used a bunch of GETPIVOTDATA functions to pull the values from the PivotTable (although I could easily have one with an Index/Match combination or something else too.) This section looks like as follows (notice the override cell):
  • Depending on the complexity of the model, I might also create a summary section to re-summarize all the inputs. This would also give the user a place to review for reasonableness, as well as give a nice range to use for an INDEX(MATCH(),MATCH()) combination. You can see tha the summary version uses my override, not the original PivotTable value here:

• Finally, we go back to the original data table we uploaded to PowerPivot. At this point, we need to have it read the data from our adjusted data tables. In the case of the example here, I used an Index/Match setup to read from the data table immediately above.

And now we have the ability to generate our writeback. We start at the top of the image below, where the blue circles are manual steps and the red circles are automatic.

You can download a sample of the setup I used to test this. It’s fairly simple, but it does demonstrate that it works. J

Last week I was working through creating some more DAX measures. And while I was successful, I still don’t feel that I’ve really truly wrapped my head around how they work. (I notice that Dick Moffat feels similarly, too.)

The biggest thing that I had to wrap my head around is that Date/Time DAX functions are completely unreliable unless you bring in your own complete table of dates. Otherwise, if there is a single date missing in your table of dates, it completely blows apart the opening balance formulas. I really struggle with this… I don’t have to build data tables in Excel to be able to use its date/time functions, and I’m not sure why I should need to for PowerPivot. At any rate, I built a table into a SQL database and import it along with the rest of my other tables now. I contains every single day from January 1, 2003 to December 31, 2011 at this point, so I can avoid that issue. (Our history goes back to 2003.)

Something else that struck me as odd is that, in order to create a measure I can only do it on a Pivot Table. Yet it links back to the tables in the PowerPivot data. When you create the measure, you get to pick which table stores the info.

So here’s what I don’t get about this. Why do I need to have a Pivot Table in place before I can kick off the “New Measure” button?

The assignment of Table name is also a bit… it’s not a mystery, it just feels weird. I see that you’ve got two options:

1. The DAX measure can be placed in any table provided that the columns referred to can be traced back through the relationship chain to the originating table. The problem I run into here is that when you’ve got 10 tables in your PowerPivot file, it can be painful to hunt down the measure definitions in the field list when you want to find them. (It also kind of defies logic to me that it doesn’t need to be somewhere.)
2. If you fully qualify your table names (as I have above), it seems that it doesn’t matter where the heck you put them. So to keep things organized I created a linked Excel table called “tblMeasures” with one cell of data. I could then assign all of my measures to that table to keep them organized. The only issue is that I’m now stuck with the message “Relationship May be Needed”. I’d sure like to be able to say “thanks, but you don’t need to bother me for this table”

Ultimately though, it would be really nice to have some kind of section/filter to display the measures vs the tables/fields.

I will also say that I find the DAX editor to be somewhat wanting after the richness of the Excel UI. Maybe it’s just me, but I don’t find the term “expression” in the Intellisense all that helpful when you’re trying to learn how to write one of these formulas. (Some kind of expression builder would be really cool, but I imagine that would also be kind of tough to implement.) The other issue I find is that these things returns tables of information, then distill pieces out by filtering, summing, etc… When you’re stuck, and something isn’t working out how you think it should, it is VERY difficult to see where you went wrong.

Breaking a formula up into multiple lines kills the Intellisense outright too, which is very frustrating. Once the formulas start to get complex, this is sometimes the only way to keep them legible. And the fact that the case for DAX isn’t updated to all caps once committed is kind of an annoyance. I don’t type in caps, but I never realized how much I appreciate Excel converting those for me. It sure makes it easier to read afterwards.

Finally why is it that every time you get a message it obscures the note in the box below?

At any rate, I’m sure I will get my head wrapped around this, it will just take time. J

Over the last couple of days I’ve been trying to build some stuff with PowerPivot and I’ve come up with some other things that I would find pretty helpful. Here’s one of them.

One of the big challenges I found was trying to figure out why my PivotTable was returning the results it was, instead of what I was expecting. Part way through my troubleshooting process I began to doubt the way I’d set up the relationships between my tables, so I decided to take a look at them. I found myself staring at this table:

The problem is that it’s really difficult to visualize data in this format, and I found myself longing for a good old Relationship diagram like we can build in Access. I did pretty much the only thing I could and reached to Visio to draw this one up:

The one side of the relationship is shown with the +, while the many is shown by the circle and lines.

This is the final version, but the mapping of the prior showed me that I’m made some errors in the relationship flow. Even this version actually turned up a couple of things… tblCOA is the centre of a couple of many to many relationships.

The challenge with this is that it took me quite a while to draw up as I had to document each link, draw the table, then move everything around so that it wasn’t a crazy mess. It would sure be nice if I could just click a button and see this in a graphical view like in Access.