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…
I guess most of the readers of PowerPivotPro.com will already have a pretty good idea that Power Query is awesome. I spent time recently thinking about how Power Query has really opened up the data on the Internet and made it more accessible to us all. It reminded me of something I worked on a couple of years ago when I first discovered Power Query. I want to share my journey from back then to demonstrate the point about how Power Query really “Super Charges” the Internet – and because the example is just cool.
First the background
The long story short is that I was frustrated one day when SalesForce.com released its “Spring” release of its software. Now as someone that lives in the southern hemisphere, giving a software release a “northern hemisphere” season for a name is totally meaningless – let alone completely confusing. Anyway in my rage (and spare time), I set about trying to find out what percentage of the world population experiences “southern hemisphere” seasons (go figure why! – I’m just like that sometimes).
Anyway, in my journey I found a link to a NASA website that provided tables of data with the total population of the world at every 1 degree of longitude and every 2 degrees of latitude. So basically it was just a big text file 360 columns wide and 180 rows long, with each number being the population in that 2 degree square surface area of the earth – just what I needed to check the population in each hemisphere.
by Matt Allington Today I am sharing a trick that I have used a number of times for clients – it allows you to use simple VBA to jump from one pivot table to another, and when you arrive at the second pivot table it is automatically filtered to show the context for the data you want to see. It is a great user experience.
Here is a sample use case and demo
You are browsing and drilling into a product hierarchy looking at the change in performance vs last year. You drill down to the product level and want to see the weekly sales for that particular product. You simply click on the “show detail” button and you are taken to a detail page that shows you the details for the specific product you were looking at. Let me share the process of how to build this interactive report.
Use the VBA Recorder to do the heavy lifting
After I set up my 2 Pivot Tables, I recorded a couple of steps with the VBA Recorder to help me write the code. Here is the process I followed.
In my last blog on PowerPivotPro.com I showed how easy it is to create a standard calendar for Power Pivot using Power Query. Most of my customers however don’t use a standard calendar but instead use a 445 calendar (which is very common in the Retail Industry). A lot of people shared with me their 445 calendars after my last post, and that reminded me that although the concept of a 445 calendar is very common, everyone seems to have different rules on how the calendar works.
Differences in 445 Calendars include
- What month you start the financial year
- What day of the week is the start of the week.
- 365 divided by 7 = 52 + 1 day remainder. Different companies handle the extra day in different ways.
So there are almost as many permutations as companies and I don’t want to go down the path of trying to write 1 calendar that will work for everyone. Instead this post covers the techniques I used to solve the 445 calendar problem for one of my customers. If you are so inclined, you can copy these techniques plus some of your own to meet your own 445 needs. To give you an idea, it took me about an hour to think through the problems, research the functions and build a working calendar. If you copy my techniques, you should get a head start on that for your own 445.
The rules of this particular calendar are
- The first day of the financial year is the Monday on or before 1 May.
- The number of weeks each month are in the pattern 454 (4 for May, 5 for June, 4 for July) and then repeats.
- The calendar weeks, months and years mirror the financial data. So Mon 31 Dec 2013 is the first week of financial week 36. Even though the date is actually in the year 2013, it is treated as part of the first calendar week of 2014.
Here is how I did it. But first a word of warning. The steps are easiest to follow if you first download the sample workbook (link at the bottom) and step through the Applied Steps as you read my explanations in this post. If you are not a Power Query expert and you just read the post, it will quite hard to understand. So do yourself a favour and download the workbook, and step through it as you read the post
In this post, I am going to show you how simple it is to create a custom calendar using Power Query. If you follow the steps below, you will get a good sense of how capable Power Query is, and may spark your interest to learn more about it.
Different Types of Calendars
There are lots of different ways of creating a custom calendar for use in your Power Pivot workbooks, and plenty of discussion about them on PowerPivotPro.com (25 prior to this one). Historically I have just used an Excel workbook that I created with all the relevant columns, and enough rows of dates to cover for the next year. There is one problem with this approach however – the date over runs when you are looking at last years’ data in a pivot.
As you can see above, we have dates into the future that are showing sales for last year with no sales this year – because we haven’t had “this year” yet. It is a pain to change the import filter on your Excel calendar each time you refresh (not practicable actually). You can write some snappy Excel functions in your source calendar table to code around this, or you could make your DAX more complex, but better still – I think this is a great opportunity to learn some new skills – Power Query.
Building a custom calendar from scratch in Power Query is actually quite straight forward, and I am going to take you through it step by step below.
First Create a New Blank Power Query Workbook.
The Blank Query option is right at the bottom of the “From Other Sources” menu.
If you haven’t done so already, turn on your Formula bar from the view menu. You will need this so you can easily see the Power Query Code that is generated by the UI tools, and also to allow you to edit the code by hand when needed.
I was recently helping a forum member at http://powerpivotforum.com.au with a problem about how to dynamically calculate an employee’s age. I thought a worked through example would make a good blog post as it demonstrates a further use of disconnected tables vs the more common “disconnected slicers” tables that Rob loves so much.
First let me explain the scenario.
Number of Employees Under the Age of 35
The requirement is to be able to calculate the total number of employees under age 35 years of age at any point in time. The DAX formulae therefore need to take into account new employees starting at the company, employees leaving the company, as well as the fact that all employees get older every year. Here is the solution I created.
by Matt Allington Many businesses (dare I say most) are yet to get started with Power Pivot and Self Service BI. Often the first person in an organisation that discovers Power Pivot doesn’t know how to navigate the political and cross functional challenges within the organisation to make it all possible. The tragedy of this are the missed opportunities – lost time is lost money. There are huge benefits that can flow to businesses if they could just get this happening. There are benefits for IT and the overall business, including lower IT costs, faster speed to value realisation and indeed green field analyses that would not have even been contemplated as feasible without a Self Service approach and a tool like Power Pivot.
How to get started?!
I am well under way in my career as a Power Pivot Consultant and Trainer. And I have to say (now that I have delivered a number of Rob’s “PowerPivot for Excel” training classes) that I am finding delivering training to be one of the most rewarding things I do.
It occurred to me recently that people like me (and also Rob, Avi, Scott), that train users in PowerPivot are able to glean useful insights into the way people learn (and incorrectly learn) PowerPivot. Today I am going to share with you 5 common mistakes that I have personally observed – maybe you will identify yourself in some of these things, or maybe you will confirm that you are doing just great. Either way, it is worth a read to either discover a gap or confirm your skill.
But first: I have trained 2 general types of students
I have found there are 2 groups of students that sign up for my PowerPivot for Excel classes. There are students that are very new, very green (see what I did there – green, get it?) and are using the class to get started. They come in with very little knowledge about PowerPivot, but enough to know that this could be something awesome. In the second group are students who have a reasonable amount of PowerPivot experience under their belt but realise there is more to it. What is a “reasonable amount” of course can vary, but I would classify these people as “active” for 6-26 weeks with a total number of “invested hours” in the vicinity of 10 – 60 hours or so. Often they are struggling to move forward, and this post covers the main reasons why.
I always ask my students to rank their knowledge on a scale of 0 to 10, and they normally rank themselves 0 (newbies) or about 3 or 4.
Recently I have been building some interactive charts for one of my clients using techniques that I have learnt from powerpivotpro.com, from searching the Web, as well as some of my own ideas. While some of the techniques I will talk about in this post are not new, I have combined some of these old favourites with some new techniques to solve some of the problems I have come across. I want to illustrate how the combination of these things can deliver a very positive user experience, and just as importantly – anyone with a good set of Excel skills can build an interactive charting tool like this by following the patterns demonstrated. I have created a demo of all of these concepts into a new workbook using Adventure Works so that you can see how these techniques come together for the user. There is a link to this workbook at the end of this post.
The techniques I have used are:
- Disconnected slicers used to create interactive chart series
- Cube formulae and standard Excel to make an interactive chart title
I love these 2 tips I learnt from Rob – so user friendly. However I came across a few problems when I tried to implement these, hence I have developed the following 3 additional techniques to solve these problems.
- Cube formulae and standard Excel to make an interactive legend
- VBA and “link to source” for interactive axis formatting
- Excel VBA to change which Axis the series appears on.
I have created a short video to demonstrate the 5 features built into this workbook, and I then explain each of these in more detail below. I have not hidden the behind the scene workings so you can see these in action – of course you would normally hide these from the user.
Now let me call out the key techniques I have used to make this workbook rock.
In my last post I talked about a mistake I made early on in my DAX learning journey. In that post I showed a formula that used CALCULATE ( ) to turn a row context into a filter context (AKA context transition). Here is a quick refresh of the relevant part of that post.
Since this post, I have been thinking about CALCULATE ( ) and wondering how to explain “WHY” CALCULATE ( ) creates context transition – this is the topic of today’s post.
I am sure there is a wide age profile of readers of this blog, and at least some of you would remember the Merrie Melodies cartoon “Cheese Chasers” where the dog (Marc) is sitting and using an ACME adding machine to make sense of what he is observing. [I know I said Looney Tunes in the title, but there is not a lot of difference and I thought more people would know what Loony Tunes was]. Before you watch this brief 40 second clip from the cartoon, let me first set the scene. Everything is backward; the mice don’t eat cheese, the cat WANTS to be chased by the dog – you get the idea. Spoiler alert – I will refer to the punch line below, so don’t read on until you watch the video if you want the full 1951 immersive experience.