Status of our Master Your Data Book

We are rapidly approaching November 1, 2020, which has been the latest release target for our Master Your Data book. This is the long-awaited second edition of M is for Data Monkey, the book that I wrote with Miguel Escobar.  I want to share an update with you as to why the book’s release is about to be pushed back – yet again.

Our Master Your Data book (new edition of M is for Data Monkey)

First, I just want to say that Miguel and I wish we did not have to do this.  We want to get this book into your hands and scheduled a big block of time in the summer to get this done.  Our challenge this time has not been around technology, so much as the same thing that has impacted virtually all of you this year: COVID.

What have we been doing, anyway?

From my side, I can tell you that I was away from home for over 155 nights last year.  I travelled a ton leading in-person training courses and speaking at conferences.  Returning to the office was dedicated to catching up on the things I had missed and trying to slot in a block of time to write was quite difficult.  And then, in March 2020, everything changed.  We hit a global lockdown, and I watched my business go from 100 to 0 in the span of one week.  Every single training contract cancelled or postponed; income flow gone.

In some ways, you would think COVID would be a blessing there… I mean look at all that free time, right?  But the reality is that I have a team of 5 people at Excelguru, and I need to pay them.  We had to pivot, and pivot fast in order to generate a new income stream to keep people employed, and this has been all-consuming since then.

We brought Matt Allington in as a partner for our Power Query Academy and rebranded the enterprise as Skillwave Training. Ever since then, we have been working on adding new content there.  During that process we have come to realize that our site hasn’t scaled from a one-course site to a multi-course site. Now we’re investing in trying to design and build a better structure for our entire site.  (No, no promises yet on when you are going to see that!)

How are we weathering the storm?

Now, I am pleased to say that all of my clients who cancelled or postponed have come back to us.  But even with the courses I’ve done dozens of times before, the commitment on my side for a one-day in-person course has now become one to two weeks of time. This is because I have to adjust it for an online format, shoot and edit the video, get it all uploaded into our LMS, and so forth.

I joke today that I am working double the hours for a fraction of the earnings… but at least I still have it better than most.  The reality for us – like many – is that 2020 has been a very hard year.  I am just super proud that despite all of these challenges, we’ve been able to scrape by.  I have been able to keep my entire team at their usual salaries.  It is tight, but we are making it work.

Of course, there are two authors for this book. And while Miguel’s story is somewhat different, it is also somewhat the same.  He has been scrambling to deal with his business challenges – working sometimes 20 hours per day – in order to meet his commitments.

Where are we at with the Master Your Data book?

The simple reality here is that we just do not have the time to be able to write the Master Your Data book.  Book writing has never been a lucrative business, we do it for the passion of sharing with all of you.  So we are trying to build our businesses back to the point where we can do that again.

I know what you are thinking: "COVID, Murder Hornets, and now this.  2020 sucks!"  Yes, it sure does.

Having said that, we were asked if we should just cancel the sequel.  Miguel and I both said no to this.  We have made commitments to many people.  We are very proud of M is for Data Monkey and we want to make sure you get the new volume that you have been waiting for.  While the Master Your Data book won’t be released in 2020, we hope that we can at least brighten your 2021.

What can we expect going forward?

Last week Miguel and I sat down (virtually) to work out our future plan.  We are both committed until the end of 2020, but feel that we should be in a good place to start writing by mid-January.  Here is our plan on how it is going to play out:

  • Mid-January (at the latest), we will begin writing.
  • We will deliver all chapters – once written – in draft form to active members of the Power Query Academy. (Yes, you will get a chance to review them before the general public.)
  • We will review comments and edits from the Academy members and incorporate as appropriate.
  • We will deliver chapters to our publisher as they are completed.
  • Our target completion date is March 31, 2021.

After that, there's time for page layout, copy editing, and a bunch of other stuff, but the Master Your Data book should be available from Amazon as of July 1, 2021.

Final Thoughts on our Master Your Data Book

Again, at the end of the day, we really wish that the book was being shipped to Amazon today.  We know that many of you have been waiting for this for a long time, and we very much appreciate your patience and understanding.  And if you have lost faith in us and you cancel your pre-order, we totally understand and accept that too.  We are committed to getting this in your hands, it is just taking us far longer than we had hoped.

Keep safe out there everyone.  We will come through for you.

Use Excel Tables to Filter a Power Query

A question came up in the Excelguru forums today about how to use Excel tables to filter a Power Query.  While Power Query can't read a filter from an Excel table natively, there is a cool little trick that you can do to flow that information through though.

Data Background

The data footprint I'm working with looks like this:

3 tables showing the original data, a table of just years, and the final output with all rows

The Data query is a fairly simple staging query, pulling the data from the Excel table on the left, setting data types, and loading as Connection Only.

The YearFilter query is a little more complicated, as it pulls the data, removes duplicates, and then drills down into the Year column (right click the header -> Drill Down), resulting in a unique list of the Years:

The YearFilter results in a unique list of years in Power Query

And finally the Sales Query, which - shown in an indented and 'colourfied' format thanks to MonkeyTools QuerySleuth - looks like this:

The M code of the Sales query shown in Monkey Tools

The important things to notice about this query are:

  • It references the Data query (no new data is added here)
  • The Filtered Rows step filters to include any item that is in the list generated by the YearFilter list
  • The Filtered Rows step had to be adjusted manually to add the List.Contains function
  • The [Year] column refers to the [Year] column of the Sales query (which flows through from the original data)

So What's the Issue?

We want to use the filter on the YearFilter table in Excel to filter our Power Query.  Unfortunately, that doesn't happen... despite a refresh, all the years are still in the worksheet after setting that filter:

Despite filtering the Excel table, the output isn't filtered

The challenge, when you are attempting to use Excel tables to filter a Power Query, is that Excel can't read the filter.  In fact, Power Query can't access any of the table's metadata about filters or the visible state of the rows.  It therefore brings in all rows from the table whether they are hidden or not.

Using Excel Tables to Filter a Power Query

The secret here is that we need a way to tell Power Query which rows are visible versus which are hidden.  Something we can do by leveraging the AGGREGATE function, since it has the ability to count only visible rows.

The formula I used was =AGGREGATE(3,5,[@Year]) where:

  • 3 indicates the COUNTA() function
  • 5 sets it to ignore Hidden rows
  • [@Year] points to the current row of the Year column

The weird part, if you've never done this before, is that all the visible rows in Excel will always show a 1. But look what happens when you filter to only a couple of years, then edit the YearFilter Query and select the Source step:Using AGGREGATE, Power Query lets us see the visible and hidden rows in the table Boom!  We can see which rows are visible (indicated with a 1) and which are hidden (indicated with a 0).  This now becomes a pretty easy fix:

  • Filter the Display column to 1

And you're done.  The rest of the query will still work, as it drills in to the list of years, so we don't even need to remove this new column.

And just like that, we can now use Excel tables to filter a Power Query:

Setting a filter on the Excel table now filters our Power Query

 

Update to Monkey Tools QuerySleuth

We've been kind of quiet here, but we're excited to announce that we've just published an update to Monkey Tools QuerySleuth feature.  It now contains an "tabbed" experience so that you can easily flip back and forth between queries, "pinning" the ones you want to see and compare.

The Updated QuerySleuth Interface

In this case you'll notice that I pinned The ChitDetails and ChitHeaders queries, then selected the Locations query from the left menu.

An image of the update to Monkey Tools QuerySleuth showing the new tabbed interface indicating two pinned queries and two modified queries

Why does this matter?  Did you notice that the ChitDetails and Locations tab names are both red?  That's because I made changes to both of them to update a data type... I can now hold onto those changes as I flip back and forth between JUST the queries I want to keep in focus.

Updating Multiple Queries

But now, of course, I want to commit my changes and force the data model to update to reflect those changes.  In this image, I'm doing just that, with three queries:

An image of the QuerySleuth prompting the user to ask which queries they want to save and refresh

And due to the selection pointed out by the arrow, each of these queries will not only get saved back to the Power Query engine, but a refresh of each query will be triggered as well.

So how do you get this update to Monkey Tools QuerySleuth?

This update to Monkey Tools QuerySleuth is available in Monkey Tools 1.0.7553.5975 or higher.  And it's available in both the free and Pro versions of the tool.  (Of course, you will still need a Pro version in order to actually save your queries.)

To try our free trial, head over to the Monkey Tools product page to download your copy.

If you already have Monkey Tools installed, it will automatically update within a couple of weeks, or you can request the update now by going to Monkey Tools -> Options -> Check For Update Now…