As you may have heard, we have been working on a set of helpful Power Query Recipes for Excel and Power BI and are excited to announce that they are now officially available!
Are our Power Query Recipes for you?
The Power Query Recipes are targeted at people who are familiar with the Power Query interface (in either Excel or Power BI), and will lead you step-by-step through the process needed to clean up and convert your data from one format into another. And if you're not already comfortable with Power Query? Consider joining the Power Query Academy so we can change that!
So what is in the Power Query Recipes package?
There are currently over 30 cards in the set, showing easy-to-follow steps that will deal with a variety of common data issues. I personally am finding them super useful, and often refer back to them when I'm helping people clean up their data in person or in forums.
We've even marked each of the Power Query Recipes that has a video version in Power Query Academy. If you are already subscribed to our Academy, simply click the video camera in the bottom corner, and it will take you straight to the appropriate video so that you can see the technique demonstrated in a live setting.
Are there samples of the Power Query Recipes?
Of course there are!
Here's an example of one of my favorites, which lays out how to create Full Anti Join, something that does not exist in the regular Power Query user interface:
And another which shows how to create a Calendar Table on the fly:
We actually have a sample package available which includes four recipe cards (including the precedent card for the calendar recipe shown above), as well as the full table of contents for the current version.
Just some of the patterns included in the full version are:
Splitting data into new columns or rows
Pivoting, unpivoting, and transposing data
Several different ways to merge tables (including the Full Anti Join shown above)
Extracting a new column based on values in the prior or next rows
Six different ranking methods
Creating dynamic calendar tables
Adding a random number to all rows
Will there be more Power Query Recipes?
Oh yes! While there are already over 30 recipes in the set, we already have another 20 on our "to-do" list, and are adding to that list regularly. Our intention is to release new recipes on a regular basis, putting even more Power Query resources at your fingertips.
Pre-order a copy of the new 2nd edition of our M is for Data Monkey book from Amazon by September 30, 2018 and receive a FREE set of the initial recipes.**
Purchase the current stand-alone set of recipes from the Power Query Training web store for $24.95 USD. This is also an option to add a subscription ($2.95 USD every 3 months) so that you get all the new recipe cards as we expand the set.
* Please note that the recipe cards are not included in the free Power Query Academy trial, only with the full course.
**Upon receipt of your Amazon proof of purchase being received via email at email@example.com, we will provide a coupon code for $24.95 which can be applied to either the stand-alone or subscription purchase. Act fast though, as this offer ends on October 31, 2018.
Some time ago we embarked on a bit of a crusade to get Microsoft to fix a specific issue with Power Query related to performance. I posted about it in detail on the Power Pivot Pro blog, and have been encouraging people to vote on this at every possible turn. Conferences, classes, my free e-Book series, even the Microsoft Data Insights Summit - none of them were immune to hearing me drum up support to get this fixed.
Your voice matters
You voted, and Microsoft listened. I'm super excited to let you know that they have architected a fix based on your votes, and it's finally out of testing!
Even though the idea is still marked as "started" on UserVoice, (I got this directly from a reliable source,) it is starting to roll out to the Office Insider channels on Office 365. I'm told that so long as you're on version 1801, build 9001 or higher, you're good to go and have the fix in place. You can locate your version and build information via File --> Account:
Thank you for voting!
While Microsoft does listen to me, my voice only goes so far. Your voice matters a huge amount in this process, as it validates that it is bigger than just one person. I want to thank everyone who voted for this fix to raise it's importance along the way!
Can't wait for the fix?
You can get on the Insider channel, and that will get you the fix. There are two methods to do this:
Will this fix all your Power Query refresh speeds?
Absolutely not. This deals with one specific technical issue that we identified which was re-doing work multiple times. There is still much improvement that needs to be done, but at least we've got a start here to bring Excel back to parity with Power BI Desktop.
In the mean time, if your queries are going slow, you might want to consider our Power Query Academy. We have a module on Query Optimization which teaches how to use Buffer functions, provides a strategy to reduce lag during development and also shows a few settings that can be tweaked to make your code run faster.
Yes, you read that right… Power Pivot is coming to all Office SKUs and the rollout has already started for those on subscription versions of Office.
This is something I've been championing (along with many others) since Power Pivot was initially rolled in to Excel 2013. The whole need for Pro Plus licensing, which was even initially only open to enterprise licensing, was a huge mistake in my opinion.
Today, the Excel team updated the Excel Uservoice request to make Power Pivot available in all SKU's of Excel to say "we're doing it". Finally! So yes, if you're running the Home and Student, or Business Premium plans, you'll finally have access to Power Pivot!
What is Power Pivot is coming to all Office SKUs?
How long will it take for Power Pivot to show up in your version of Excel? That depends upon what you purchased…
Excel 2013/2016 non-subscription users
Unfortunately, you need to upgrade to a subscription version of Office or to Office 2019 (whenever it comes out). They're not going to back port it to those versions as far as I'm aware.
Excel Subscription users
You have two options:
Wait till it shows up. Microsoft has said that they are already rolling this out to people on the April Current Channel (build 9330 and higher). So depending on where you are in the cycle, it will just show up one day.
Install an Insider Preview in order to jump the queue. Keep in mind that this isn't for everyone as this is Beta software, but if you're interested you'll also get access to newer features like the new data types, Insights and more as well.
How do you know your Excel SKU, version and what channel you are on?
(Please note that Channel is only applicable to Subscription users)
Go to File --> About
How do you get on the Insider Channel?
It depends on the SKU of Office you purchased.
Consumer Office Versions (Office 365 Home, Personal, and University)
It’s a busy month here at Excelguru. Instead of a technical post we wanted to catch everyone up on our October news and events!
Live Course: Master Your Excel Data
Ken is teaching a LIVE, hands on course in Victoria, BC on Friday, October 21 from 9:00am-4:30pm. This session is great for anyone who has to import and clean up data in Excel and will change the way you work with data forever! Ken will teach you how to use Excel Tables, Pivot Tables and Power Query. Space is limited to only 20 attendees, so don't miss out on your chance to sign up. For full details and to register for the session, visit: http://www.excelguru.ca/content.php?291-Live-Course-Master-Your-Excel-Data.
October News and Events: Power BI Meet-up
The next Vancouver Power BI User Group meet-up is happening on Thursday, October 13 from 5:30-7:00pm. Scott Stauffer, Microsoft Data Platform MVP, will be presenting on How to Operationalize Power BI. Together we’ll look at some solutions that might help pass your Power BI solution over to IT to manage enterprise-wide. Dinner and soft drinks will be provided. View the full details and sign up to attend at: http://www.meetup.com/Vancouver-Power-BI-User-Group/events/234126999/.
Microsoft MVP Award Received
For the 11th straight year, Ken has received the 2016 Most Valuable Professional Award from Microsoft! The previous 10 years, Ken’s award has been in the Excel category, but this year’s award is in the Data Platform category. The new category reflects the work he’s been doing this past year with Power Query and Power BI. Congratulations Ken, your guru status remains assured.
Our Team Has Grown
As we mentioned the other day, Rebekah Sax has recently joined the Excelguru team. She brings with her a wealth of experience in marketing, communications, event planning and administration. Please join us in welcoming Rebekah as she helps us make new connections and continue to grow.
I’m pretty stoked to announce a big milestone for Excelguru. That’s right, we’ve added a new team member to our company!
We’re pleased to announce that Rebekah Sax has joined our team and will be helping us with our marketing efforts. She spent the last 15 years working at Fairwinds in a variety of roles from marketing to event planning (and more), and her broad skill set is just what we needed in order to fill some pretty big gaps in our practices. In fact, you can already see the effect. If you remember the Excel Courses Calendar I set up on my website ages ago… it’s actually got courses listed now!
Just a quick note here to let you know that the blog is closed this week. (We’re taking a well deserved break to enjoy some family time, and hope you get the chance to do the same.) But don’t worry, we’ll be back in January with more posts!
I also wanted to throw out a quick thank you to all of you who have been faithfully reading the blog on a weekly basis. Your support means a great deal, especially since I took the plunge to go full time with Excelguru back in May. It’s been a great year in that regard, as it’s given me the opportunity to build many things, including M is for Data Monkey. Much of the material for this book was inspired by blog comments and email, and I feel fortunate to have been able to translate those into a book that is being so well received.
We wish you the very best of the holiday season, whichever you celebrate, wherever in the world you are. See you in 2016!
I’m pleased to say that I received the email this morning to let me know that I’ve been renewed as an MVP for another year.
This is my 10th Microsoft Excel MVP award, and there have been a lot of changes in that time. Although I do provide my opinions during my technical articles, typically I don’t often create blog posts that are solely introspective or opinion pieces. With this being a milestone for me, I thought I’d do one today.
Some of the highlights since I’ve been an MVP:
We’ve seen four versions of (Windows) Excel since I was awarded: Excel 2007, 2010, 2013 and now 2016.
Excel online pioneered as a “free for everyone service” (which you can access at www.onedrive.com)
Office 365 was released, and Microsoft is pushing hard to make this the new model for how you purchase Office in future.
Excel for iOS and Android hit the scene. (iPhone was in it’s infancy back in 2005.)
Power Pivot was released – first as a free download for Excel 2010 (Microsoft did free?), then a second free iteration for Excel 2010, before finally becoming integrated into Excel 2013 and up.
Power Query was released, again as a free download for Excel 2010 AND 2013 (not only the current, but the past version?), experiencing MONTHLY updates (rather than major versions only) before being integrated into Excel 2016.
Power Query is continuing to get updates even after Excel 2016’s release, meaning that we’ve got upgrades continuing to be provided for 3 versions of Excel for the first time ever.
If you’d have asked me to predict any of that back in October 2005, there’s no way that I could have. Whether you agree with them all or not, these changes have been revolutionary for both business intelligence in Excel, as well as the ability to connect from anywhere to get your work done. (Or feel guilty about not getting it done!)
Of course, not all of these have been without flaws or problems. These are just my opinions, but…
The pioneering of the Ribbon in Excel 2007 without a way to customize it was a disaster that should never have happened. Microsoft was so focused on “their” new user interface that they didn’t even want to hear that it was less efficient and punished power users efficiency. So much so that I wrote a book on it. Thankfully, the product teams listened eventually, and we saw that change in Office 2010.
Office 365 has been met with confusion and angst. I think there’s been a couple of reasons for this. The first is that they’re pushing a new subscription model, conflicting with over 30 years of Microsoft’s habitual model where we were able to buy once and “own” the product. The second is that they’ve done a poor job of explaining that the Office 365 service is actually a bundle of products in many cases, not JUST your Office license. I use Office 365 to host my email and even have a Sharepoint site. There is no way that I could afford to run and maintain my own email/Sharepoint servers, and last time I checked - based on a 3 year update cycle – the cost was about the same to subscribe vs re-buy anyway. Overall, I think the subscription is actually the right move, it’s just going to take time for people to get used to and embrace it. It’s this model that will allow Microsoft to justify giving us constant and frequent updates.
Re-branding of the “Power Add-ins” was a huge mistake. When Power Pivot, Power Query, Power View and Power Map were released, I met MANY high end Excel pros who celebrated that Microsoft had FINALLY recognized their role, and was releasing Power Tools aimed at them. (For reference, I know the Excel team DOES recognize the value of power users, this was simply the perception of those I met.) Now we’re seeing these tools re-branded down to “less intimidating” names: Power Query in Excel 2016 has basically had it’s identity removed, Power Map is now 3D maps, and Power View has been hidden away without a default place in the Ribbon. I can’t say how much it frustrates me that there is a constant need to dumb down the interface and pretend that Excel is easy. It’s not, and the team at Microsoft that controls the branding needs to recognize this. The Excel pros that drive the majority of business intelligence need both the tools and the respect for what they do. There is a vast amount of the program that is perfectly accessible to the 80% who use 20% of the program. Let’s put some focus on the 20% that drive the majority of business intelligence world wide.
Now, having said all that, I want to point out that overall I’m really happy with where Excel has gone. The Power products were real game changes for me. So much so that I quit my day job in May to focus on ExcelGuru Consulting Inc, primarily on providing training services to help people get the most out of Excel. We’ve got some great Excel courses available at GoSkills, and of course our awesome Power Query Training Workshop which will blow your mind.
Over the past 10 years I’ve learned that the Excel team is not just a faceless group of folks working in isolation from the real world. I was very much of this opinion the first time I made a trip to Redmond… (I was newly minted, and we’d just been given the Ribbon in Excel 2007, so that will give you an idea of my mindset.) In fact, as I’ve learned over the past 10 years, the Excel team are actually a collection of SUPER passionate people who LOVE Excel, and want it to be the best it can be. I have a huge respect for all of them. I count myself very fortunate to be in a place where I get to go meet with them annually and provide feedback, much of which (combined with that of my MVP colleagues) has influenced many different features for the better.
One last thing that makes this program so amazing is that the MVP award gives many of my colleagues a reason to gather in one place annually. I’ll never forget walking into my first summit and being introduced to the Excel MVPs that had made the trip… the names of legends that I’d been learning from. And the welcome from them as they calmly just pointed out that hero-worship wasn’t allowed, as I was just one of them. So odd, humbling and empowering. Over the past 10 years I’ve made a lot of friends whom I’ve met in Redmond at the summit, and been able to fly around the world to meet even more. It’s been just fantastic. The summit is the highlight of my year, for the simple reason that I get to go and hang out with people who truly understand me.
I have no idea where the next 10 years will take us, but I’m excited to see it unfold.
This is a personal post, but it was a HUGE weekend for me where some very cool things happened. If you’re a fan of Excelguru on Facebook, follow me on Twitter, or are connected to me on LinkedIn, you’ve already seen a bit of this news, but this is a more detailed version of things.
It started with a soccer game…
We started bright and early, hopping into the car at 7:30 on Saturday morning to head down to Victoria. Excelguru sponsors the Angry Jellybeans girls soccer team, of which I’m the coach, and we were off to play in the Gorge Soccer Association’s mini jamboree. Three games over two days, and a good test for our Nanaimo based team to see how they’d fare against the Victoria clubs. We pulled in shortly after 9:00 and played our first game at 10:00. The girls came out flying, earning a 6-1 victory in their first match. Confidence was high, the sun was shining, and it was turning into a great day.
We had some time to kill before our second match at 2:00, so we all hung out in the sun for a while, had a hotdog, and just enjoyed the day. I hung out with my daughter for a while, just chatting. I don’t remember most of what we talked about, but I do remember this… “Daddy, why do you have to go to an awards presentation tonight? I want you to stay with us for the team dinner.” It’s the kind of thing that kills you, you know?
As a bit of background here, my boss had nominated me for Vancouver Island’s Top 20 Under 40 awards. The award recognizes the top 20 business and community leaders under 40 years of age on Vancouver Island. I’d been shortlisted to the top 100, but the awards banquet was Saturday night in Courtenay… about a 3 hour drive from Victoria where we were playing. Fortunately I’ve got a great co-coach in my friend Scott. Scott agreed to look after the team dinner, my in-laws got a hotel room and kept my daughter overnight, and Deanna and I were set to head up to Courtenay immediately following the game to make it in time for dinner.
At any rate, what can you do to a question like that? I looked at her and said “well, sweetie… if someone wants to honour you with an award, you should do everything you can to show up. It’s only polite.”
She nodded, not happily mind you, but she accepted it. Then came a question from behind her: “Coach Ken, what award are you going to win?”
The question to them wasn’t IF I’d win, it was WHAT I’d win.
The funny part is that I hadn’t really told a lot of people about this at all. I’d been shortlisted to the top 100, but I was actively trying to convince myself that I would be okay with that, even if I didn’t get recognized in the top 20. That’s a hard battle to wage with yourself when you are as competitive as I am. Really hard. I don’t settle for short of the mark. The award said Top 20… to me, that was the mark. If I’d never been nominated, I wouldn’t have lost a minute of sleep over it… but I was… and that meant the prize was top 20. And yet, I saw the bios of the other finalists, and they were incredible.
I laughed it off, and pretty soon it came time to square off in the next match so we were back to the pitch.
The second game… well… it didn’t start so well. The girls didn’t play nearly as well as they should at all, and we were losing 0-1 at half time. Scott and I pulled them in, had a little chat about how they were playing and how we knew they could play. It was pretty cool, actually… we coached and they listened… and the second half was a different story. The girls turned it on, scoring three, conceding one, and ending up with a 3-2 come from behind victory. We were 2-0-0 on the day.
Equally cool though, was what happened on the sidelines. We play 6v6 (including a keeper). Since I keep my keeper in net the entire half, that meant I had 5 girls on the field to sub on and off, and 6 on the sidelines. We’d taken to subbing 3 at a time. If it was a corporate environment you would have called it a succession plan… and the effect was interesting. 6 of the girls decided that they should do some cheering… Who’s going to win? Not the King, not the Queen, it’s the Angry JellyBeans! 3 girls went on the field, 3 came off. And the three who were still on the sidelines pulled in the new arrivals… a new round of cheering started. Then 3 went on and 3 came off… and the cheering started again... and again… and again…
It was very cute. The cool part was this though… our opponents started a cheer of their own… and their coach was wide eyed. “I’ve never heard these girls cheer before… wow!” My Angry Jellybeans inspired the other team to do something they’d never done before. Pretty awesome.
Game done, and with a 2-0-0 record, Deanna and “Coach Ken” hopped in the car, hoping to go 3-0-0 on the day.
The Top 20 awards
We pulled in to the Old House at about 6:10, and I hopped out of the car still dressed in soccer pants and my Angry Jellybeans hoodie. Working my way through the tuxedos and evening gowns I reached the front desk. The clerk asks “Are you checking in”. I nodded, and he pulls the last folio off the counter behind him. “You must be Ken… you need to change!”
I laughed, but he was right. We bolted to the room, changed superman style, and were out the door about 10 minutes later.
After a quick drive to the Filberg Centre, we parked, hoofed it up the stairs and… holy crap! There was a red carpet rolled out for us… wasn’t expecting that! I also wasn’t expecting to be given a glass of champagne as I walked in the door either… made me kind of feel like a movie star!
Because of the late arrival from the soccer tournament, we missed most of the mix and mingle time, but we still had time to chat with Russell (Fairwinds Asset Manager), his wife Mary, Jim (my boss) and Julie (Jim’s wife) who had all come out to support me. A couple of drinks, some chatting about the day, and a little conversation with a couple of nice guys that rounded out our table of 8 made for a good wind down time from the crazy drive we’d just experienced to get there.
They also gave us a nice full colour book of all the nominees and their bios. We started flipping through them… which only helped to make me more nervous!
Next up were a few speeches including one from Premier Christy Clark, who had skipped her son’s hockey game to come and talk to the finalists before the awards. She had a great speech that had the crowd laughing. Not political, which was nice, but rather focused on us. You can see some of that in the Chek News coverage. Next came dinner, then they got to the awards.
Each of the top 100 finalists was given a nice framed certificate to hang in their office. Here’s a picture of Deanna and I (smartphone shot, so quality not great), with mine:
And then came the announcement that they were going to start announcing the Top 20… Russell slams his hand on the table “A hundred bucks on Ken!”
So here’s all this support around me, all this encouragement, all this faith… and me still trying to convince myself that it’s okay if I don’t make the Top 20.
And then it started to roll out, Oscars style. They called Troy Wilson. As he’s working his way to the stage they’re reading a more complete bio. Troy makes his acceptance speech, and they call another name. The process repeats and they call another, and another… and another…
And I’m telling myself “It’s okay. Top 100 of all under 40’s on the Island is awesome.”
… and another, and another…
“You don’t have to WIN everything you know…”
… and another …
“It’s a hell of an achievement, be proud!”
… and “KEN PULS!”…
The words that came out of my mouth at that moment were deep and profoundly inspired…. “Holy Shit.”
I can actually say that I was a bit in shock. But I managed to get up there and put together a speech on the fly that I’m pretty happy with. You can see it here:
And then something remarkably cool happened. As I was heading back to my table to show off the trophy a young guy stops me… I can’t remember the exact words, but it went something like this:
“Ken, I just wanted to tell you that I took one of your Excel courses a couple of years ago. You really inspired me, and I decided to pursue my accounting designation. I’m now enrolled and pursuing my CMA. I’m a huge fan of your website and spend a lot of time there too. I just wanted to say thank you.”
To me, that is the ultimate compliment. I was so stunned I never even got his name, but wow. That was inspiring. (If you read this, drop me a line!)
Then back to soccer…
After the awards were all presented we returned to the room to get some sleep, turning in around 12:30 AM. The alarm was supposed to go off at 6:00 AM (which felt like 5:00 AM thanks to the daylight savings time change that night.) Thankfully Dee woke up right at 6:00 as neither my phone nor hers actually went off. 45 minutes later we were back on the road heading back to Victoria for the final Angry Jellybeans game vs the Gorge FC host club.
When the girls found out that I had won they all surrounded me and gave me a great big Angry Jellybeans cheer, which was really touching. We then had a chat about our goals for the final game, which ended up with the girls deciding that they were going to “win it for Coach Ken".
My girls are a talented team, and they play hard. But what I saw on the field on Sunday was remarkable. They did everything that Scott and I had coached them to do all season long. They spread out, they were aggressive, they run into open space, they passed to each other, they connected and they scored… and scored… and scored… At 6-0 Scott and I were just about to implement our “3 passes before shooting rule” when they scored again. So in the rule went… and they scored again… and again… By half time it was 9-0.
We came back on after the half with the girls very clear that they had to connect 5 passes before they were allowed to shoot on net. It was actually great. There is nothing more amusing that seeing the look on a parents face when one of your players is standing in front of an open net, and they turn and pass the ball because they’ve only completed 3 passes of the required 5. The Gorge girls also spread out and got more aggressive. Because of that they did manage to score a goal as well, which was great. The final score ended up with an 11-1 win for us.
Again though, my girls led a 1 hour cheering session through the game. And just after half time Gorge responded. Out of nowhere I hear one of my players say “they just cheered us back… come on, let’s have a cheer off!” And sure as anything they did. They’d cheer, wait for the other side to respond, and launch into a follow up… over and over again. It was such a weird counter-point… we were creaming them on the field, and they were still having FUN. And that is just plain awesome.
My only regret the whole weekend is that I let my girls run the score up as high as they did before putting in the passing rules. I never want to beat a team by 10 goals, as that is disheartening for anyone. I want these girls, no matter the team, to play soccer year after year. But at the end of the day I can’t help by feel that this time the girls on the other side had fun anyway. They were all smiles when they shook our hands, and the coaches from the other side thanked me for my actions in stopping the slaughter while still letting them play. It really felt like a win-win.
I can’t begin to say how proud I am of my Angry Jellybeans. They played amazing, and they inspired a bunch of kids to have a great time and have fun in ways that their coaches haven’t seen before. It was an inspiring end to a great tournament, and a great weekend.
And in the end…
On Saturday night I became part of a very exclusive club… one I’m proud, honoured, humbled, and flattered to be a part of. (As this was the first year of the event, there are only 20 members to date.) What’s amazing is that I never pursued this, I just did what I do. With my Excel stuff it’s what I love to do, with my other stuff what I feel that I need to do, but regardless, I was recognized for just being me, and that is pretty cool.
And on Sunday I saw the culmination of something very cool in my amazing Angry Jellybeans. Not only the wins, but the fact that they inspired others in ways that seems like it could only come through some kind of magic.
To all of those of you have liked my Facebook statuses, LinkedIn updates, tweeted me and expressed congratulations on my award through any other manner, Thank You. It means a great deal to me. I truly feel like a lucky man right now.