Master Your Data is Now Available!

You read that correctly, the Data Monkey has landed, and Master Your Data for Excel and Power BI is now available in PDF format from your favourite online bookstore!

You may have noticed that the past few months have been pretty quiet on the Excelguru blog.  A big reason for this is that I essentially went offline to focus on finishing this book.  As many of you know, it’s been pushed back before (more than once), and we wanted to ensure that this would not happen again.  I’m pleased to say that the final version has now gone to the printers, and we’ll see physical copies start being distributed by Mr. Excel and Amazon on November 1, 2021.  The even better news however, is that you can get your hands on a digital copy today, as Master Your Data for Excel and Power BI is now available for sale at Skillwave.Training.

What’s in Master Your Data for Excel and Power BI?

Miguel and I are super proud of what we’ve put together here.  This has been a very long journey to get to this point, and we wanted to make sure that we delivered the best book we possibly could.  With the exception of a few of the paragraphs in the Foreword and Chapter 0, the book has been re-written from scratch, covering much more material than was covered in M is for Data Monkey.  How much more?  M is for Data Monkey was 226 pages long.  Master Your Data for Excel and Power BI clocks in at 369 pages in total.

To give you an idea of the topics we covered in this book, have a quick peek at the table of contents:

• Chapter 0 - The Data Revolution
• Chapter 1 - Power Query Fundamentals
• Chapter 2 - Query Management
• Chapter 3 - Data Types and Errors
• Chapter 4 - Moving Queries Between Excel & Power Bl
• Chapter 5 - Importing from Flat Files
• Chapter 6 - Importing Data from Excel
• Chapter 7 - Simple Transformation Techniques
• Chapter 8 - Appending Data
• Chapter 9 - Combining Files
• Chapter 10 - Merging Data
• Chapter 11 - Web Based Data Sources
• Chapter 12 - Relational Data Sources
• Chapter 13 - Reshaping Tabular Data
• Chapter 14 - Conditional Logic in Power Query
• Chapter 15 - Power Query Values
• Chapter 16 - Understanding the M Language
• Chapter 17 - Parameters and Custom Functions
• Chapter 18 - Date and Time Techniques
• Chapter 19 - Query Optimization
• Chapter 20 - Automating Refresh

One of the biggest changes you’ll see up front is that in Master Your Data for Excel and Power BI, we tried to make sure we covered a lot up front about getting started with Power Query, understanding errors and query management.   It wasn’t until we got through those topics that we dove into specific data transformation techniques.  This was a slightly different approach to what we did with our first book.  While query management was covered briefly near the end of M is for Data Monkey, we changed that up this time, as we wanted to make this a practical guide that sets our users up for long term success as they build their solutions.  Mastering your data is about much more than just getting a transformation complete – it is about making sure that you can re-use it in future.

The Tricky Bits (for us)

When we were laying out the book, Miguel and I had a few different goals:

1. Make this an awesome resource for beginners
2. Include great material to up the game of intermediate users as well as seasoned pros
3. Create a useful resource that you’d go back to again and again
4. Write a book that will survive longer than six months
5. Deliver the book without delaying it again

Each of these is a challenge in its own way, of course.

The last point on the list is not totally in our control, of course.  In fact, less than an hour after I posted on my personal Facebook that the first draft of the book was done and sent to the publisher, Microsoft announced a visual refresh of Office on their blog.  (Fortunately, all screenshots that rely on the Excel ribbon have been updated to reflect the new design, putting that to bed for now!)  Naturally, new features will get added to Power Query, but our hope is that this book will still be accurate and useful for years to come.

With regards to making this an awesome resource for beginners, we have been teaching Power Query to beginners for a long time via our Power Query Academy, as well as in-person courses.  We have taken lessons from those experiences when deciding how to approach the material, balancing the speed and techniques used as we move through the material.  We’re confident that a new user to Power Query will be able to find the material accessible and approachable, and that the material in these pages will change their (data) life forever.

Building a book that also answers the needs of the intermediate and seasoned pro is a lot tougher, especially when trying to keep the material accessible to newer users.  We believe we’ve hit that balance and have added a ton of material to help people get deeper into Power Query.  From deeper explanations of Query Folding and the Lazy Evaluation engine, completely redesigned chapters on the M language and material that deals with Privacy and the ever-irritating Formula Firewall, we are sure that even the most seasoned pro will pick up some tips and knowledge from the material.

My Favourite Chapters

Honestly, I’m very proud of how everything came together, as well as the journey that we lay out for the reader.  But like anything that I put together, there are some chapters that I feel especially good about.

Chapter 2 on Query Management was something that I have wanted to include ever since I started teaching my Dimensional Modeling courses.  The section of that course is always an eye opener and I’ve lost count of how many times people tell me that it has changed the way they approach building their solutions.  It’s awesome to be able to put that information in writing, in a place that all our readers will be able to see it.

Chapter 9 on Combining Files is pretty awesome too.  It follows the steps outlined in my Power Query Recipe cards and will probably be one of the most impactful chapters in the book for many users: especially those who receive files on a monthly basis that need to be cleaned and combined.  The whole Combine Files experience was released to Power Query about six months after M is for Data Monkey went to print, so it’s great to finally be including the modern experience in Master Your Data for Excel and Power BI.

Chapter 10 is one that I’m particularly jazzed about.  My initial page estimate for this chapter was 12 pages… it ended up taking 21.  In that chapter we cover every native join that Power Query provides, plus a discussion on the Full Anti Join, Cartesian Products (cross-joins), Approximate Match joins and a comprehensive coverage of Fuzzy Matching.

Chapter 17 on Parameters and Custom Functions is another one that I just love.  Within the last two weeks I’ve actually reached back into that chapter twice on a personal basis to build solutions for clients.  We cover how to manually rebuild the whole Combine Files experience that Power Query does for you, and then build some increasingly complex examples, eventually landing on an improved fnGetParameter function that is built from scratch.  Super cool stuff.

Then there is Chapter 18, which begins with building several varieties of dynamic Calendar tables.  We’re not just talking about a list of dates, we’re talking about the patterns needed to build 12-month calendars with non-standard year ends, 445 calendars and more, including the columns that return the period IDs and dimensional fields for each. (These are the same patterns that we build with Monkey Tools!)  But that’s not all that is nestled in Chapter 18…  I also snuck in some methods to answer some questions that I get frequently from accountants: “How do I allocate my sales/expenses over x periods?”

There is so much material that we ended up getting into this book – some of which has never been seen before… I can’t wait for people to see it.

Getting Your Copy of Master Your Data for Excel and Power BI

If you are an active member of our Power Query Academy, or my Self Service BI Academy, you’ve already got a copy waiting for you in your Skillwave dashboard.  And if you are an alumnus of the Academy (your subscription is no longer active), you’ll be getting an email over the next week to let you know how to claim your copy.

Physical copies won’t be fulfilled by until Nov 1, 2021 but can be pre-ordered via either the Mr. Excel store, or Amazon.  But if you’d like to pick up the PDF version, you can do so right now at Skillwave.Training.