Latest Monkey Tools Roundup

An email from a client this morning underscored the need for a blog post that provides a Monkey Tools Roundup.  With the amount of time and effort that went into Master Your Data, I sorely neglected the blog and specifically telling people about the new features that we’ve released this year.  So let’s take a quick look at the highlights of what we added to this tool in 2021 (so far).  And if you don’t have Monkey Tools yet, you can download a free trial here.

Monkey Tools Roundup - Summary Jan to Aug 2021

Monkey Tools automatically checks for updates every couple of weeks, and most of the time you’ll find that a new one is available for installation.  Once thing we don’t do a very good job of, however, is telling you what’s in them.  Often times they are just bug fixes, but they also include new features.  Typically we’ll announce those during user group presentations with a demo, but not always.

Release Summary

Here’s a summary of the major releases we’ve released so far in 2021:

Date Release Details
2021-08-06 1.0.7888.30374
  • Internal rewiring of Monkey Tools' brain to make it smarter
2021-07-28 1.0.7879.30981
  • GetMonthEnds Function released
  • Fixed issue where error caused Monkey Tools to miss all subsequent model details
2021-07-15 1.0.7866.42351
  • Added Pivots & Filters -> DAX Table Monkey
  • Detailed Error Logging diagnostics for Monkey Tools added to Global Options
  • Several bug fixes
2021-06-07 1.0.7828.26709
  • Fixed bug in ImportMonkey
2021-05-20 1.0.7810.32706
  • ImportMonkey Released
  • Digitally signed Monkey Tools add-in (previously only installer was signed)
  • Bug fixes
2021-05-07 1.0.7797.26285
  • Filter Context Measure Released
2021-04-26 1.0.7786.26237
  • SCD2 Monkey Released
2021-04-06 1.0.7766.22272
  • Various bug fixes
2021-03-25 1.0.7754.30938
  • Beta builds of ImportMonkey and SCD2 Monkey
2021-02-19 1.0.7717.30552
  • Bugs fixed in CalendarMonkey and TableMonkey
2021-02-08 1.0.7709.34880
  • Performance Enhancements to TimeSleuth
2021-01-08 1.0.7678.28973
  • Create Query From M Code feature released
2021-01-05 1.0.7675.29069
  • Added Option flag to enable new PivotTable buttons
  • Various bug fixes

For reference, this isn’t all of the releases, just the biggest ones.  There were about a dozen other releases scattered throughout this window as well.  Those were mostly related to fixing bugs, but also included some internal work that we did in order to improve our Monkey’s brain, as well as provide us with the ability to incorporate Beta features to specific audiences.

Monkey Tools Roundup – Features Released

A big reason for the Monkey Tools Roundup is that we’ve been a bit remiss in telling people about these new features.  So let’s look at the specific features that we’ve added to the tool so far in 2021.  We’ll start with the oldest first…

Added Option flag for new PivotTable buttons

If you are on Office365, you may have received the new experience where the Insert -> PivotTable menu no longer contains “PivotTable” and “Recommended PivotTables”, but rather is split across three buttons (as shown in item 1 of the image below).

Excel's pivot table (left) compared to the natural Monkey Tools Pivots menu (middle) and revised version (right)

We love this new menu, and really wanted to incorporate those buttons on our Pivots & Filters menu as shown in item 3 above.  Unfortunately, we have no way to programmatically identify if you have them or not, which meant that we have to serve a menu that looks like item 2.  To solve this we added an option under Options -> Global Options -> Use Legacy PivotTable Menu Buttons.  By default this is checked, returning the view shown in item 2. But if you have the left hand menu shown above, just uncheck it and you’ll have a menu that shows like item 3.

Query Monkey -> Create Query From M Code

There are many times where I copy a query and need to paste it into Power Query.  The feature allows you to do this, but also allows indenting and choosing the load destination right up front. It was designed primarily to assist with copying and pasting samples when helping users in forums, but can also be a handy form if you have a library of queries that you maintain outside Monkey Tools.

The Insert Query from M Code user form

The SCD2 Monkey

In our Dimensional Modeling course at Skillwave, we spend about an hour going through the manual process in order to solve the Type 2 Slowly Changing Dimension challenge.  This feature allows you to solve the same goal in under one minute.  It is fully configurable for keys with or without meaning. Granted, it does require some understanding of dimensional modeling, but for those who run into the issue, it’s sure to save you a TON of time.

If you’d like to understand the background of how (and why) this feature exists, you should watch the recording of Quickly Solving the SCD Challenge on my YouTube channel.  The first 48 minutes show the manual way to solve the challenge. From minute 48 on you can see the demo of this tool in action.

Import Monkey

Since the initial release of Monkey Tools, I’ve always had the goal of adding this feature.  Import Monkey allows you to import your models from either a Monkey Tools backup file or – more impressively – Power BI Desktop files, recreating them in Excel.  While there are things in Power BI that we can’t bring backwards, there is a great deal that we can recreate including queries, relationships and measures.  And what we can’t build for you, we’ll report.

For a full demo of how the Import Monkey works – as well as an overview of a ton of other Monkey Tools features – check out the webcast that I did with Reid Havens.

Measure Monkey -> Filter Context Measure

One of my students asked for this during one of the AMA sessions of my Self Service BI Bootcamp. We thought it was a good idea as they were copying and pasting back and forth from other applications to make it happen.  This feature basically allows you to create a measure to return the filter context of a given cell into a PivotTable.  I can’t claim credit for the DAX pattern itself, but giving you a variety of options to create the measure is something that we’re proud of:

Building a Filter Context measure with Monkey Tools new Filter Context Measure Monkey

And after adding this measure to a PivotTable, it nicely shows the filter context at every given row/column intersection:

Filter Context measure in action on a PivotTable showing the filter context of each cell

DAX Table Monkey

This feature was discussed and demonstrated in my Creating Unpivotable Charts presentation for the Vancouver Power BI and Modern Excel user group.  (It makes its first appearance at approximately minute 48 of the presentation.)  Basically, what it does is allow you to create tables in the Excel worksheet that retrieve values from the data model via DAX queries.  While the meet-up recording does still mention that it is in Beta, it is fully release in the software.

Detailed Error Logging diagnostics

This is honestly something that I hope you’ll never need: in order to help debug issues with Monkey Tools, we’ve added a flag to turn on detailed error logging to a file for the current session.  It can be turned on in the Global Options and is super helpful tracking down the source of bugs that people report to us so that we can fix them.

Insert a fnGetMonthEnds function

This new feature injects a handy little function which will return a table of the Month-Ends between two dates, as shown here:

The results of inserting and using the fnGetMonthEnds function is a list of Month End dates on each row

This pattern is actually super helpful, especially if you need to allocate items over a given number of months: a technique that I’m going to be demonstrating in a session called “Several Ways Since Sunday” on August 29, 2021.

Monkey Tools Roundup – What We Are Working On

To finish off our roundup, I figured I should add a quick note as to what is coming up next.  We’re not promising a lot of new features over the next couple of months.  The reason for this is that we are actually doing a lot of internal work on the Monkey’s brain at the moment.  We’re refactoring a lot of old code to make it more robust and scalable, which will allow us to add some other cool things that we are unable to accomplish currently.

Having said that, we do have some features in the pipeline as well, but it is a bit too early to share details about what they are.  If you have ideas that you’d like to see in Monkey Tools, please do share them with us.  You can do so via our Feature Request form.

I hope that this has helped enlighten you as to the new features that have arrived this year.  We’ve still got a lot of stuff on our list, one of which is being a bit more pro-active about announcing what has happened!

Label Duplicates with Power Query

Recently, a reader commented on a blog post that I wrote back in 2015.  Their question essentially boiled down to working out how to label duplicates with Power Query.  As an additional twist though, they also wanted to ensure that the first naturally occurring data point was never accidentally labelled as the duplicate.  As Power Query often re-sorts data at inopportune times I thought it was worth a look as to how to accomplish this.

The Goal:  Label Duplicates with Power Query

Our original source data is shown in blue columns below, with the green column on the right being the one that we want to add via Power Query.  (The white column on the far left contains rows numbers.  They aren’t actually part of our source data at all and are only intended to make it easier to follow the explanation below the image.)

A table with our source data on the left where we want to label duplicates with Power Query as shown in the final column

The important things to notice here are:

  • Row 2 of the table records the initial entry for SKU 510010 (Canadian), with a duplicate on row 12
  • We have an original entry of SKU 510032 on row 15 and a repeat on row 18.

The key thing that we want to ensure as we flag the duplicates in this scenario is that the sort order is always retained as per the original order of the data source.  While you’d think this shouldn’t be hard, the reality is that there are many occasions where Power Query will re-sort your data on the fly, and we cannot let that happen here.

Getting Set to Label Duplicates with Power Query

The way I would approach this task – providing that the data has already been loaded to Power Query – is to do this:

  • Add an Index Column --> From 1
  • Select the SKU column --> Transform --> Group By
  • Configure the “New Column Name” to call it “Data” using the “All Rows” aggregation --> OK

Adding an All Rows aggregation via the grouping dialog

  • Go to Add Column --> Add Custom Column and use the following formula:
    • Table.AddIndexColumn( [Data] , "Instance" , 1 )
  • Right click the Custom column --> Remove Other Columns
  • Expand all columns from the Custom column

Now, if you’ve been following my work at all, you may recognize the data pattern I just used.  It’s called Numbering Grouped Rows, as is available as one of the Power Query Recipe cards and is also illustrated in Chapter 13 of my Master Your Data for Excel and Power BI book.  The result is a data table that looks like this:

The data points in Power Query with columns added to show the original row number and the instance of each point

As you can see, the Index column preserves the original row numbers of the data set.  In addition, the “Instance” correctly records the order of their appearance in the data set.

Applying Labels to the Duplicates

This is the easy part:

  • Go to Add Column --> Conditional Column --> name it “Occurrence” and configure it as follows:
    • if the Instance column equals 1 then return the Original column else return the Duplicate column
  • Sort the Index column --> Sort Ascending
  • Select the Index and Instance columns --> press the DEL key
  • Set the data types of each of the columns

And that’s it. The data points have all been labeled and can now be loaded to the desired destination:

Our final output with duplicates highlighted

If you'd like to play with this scenario, you can find the completed sample file here.

Learning More

I love data patterns and include a ton of them in Master Your Data with Excel and Power BI, our Power Query Recipe cards.  Both of those resources are also included in our Power Query Academy video course as well, where you can actually see them performed live.  I have to say - of all the recipes I have - Numbering Grouped Rows is one of my particular favourites.  It has a ton of utility in all kinds of scenarios.

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!

Master Your Data book cover

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 Bootcamp, 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.

Major redesign at Skillwave.Training

This past weekend we published a major redesign at Skillwave.Training.  Months in the making, this has been a total overhaul to focus on delivering the best online learning experience for our clients.  Check out some of the images from the new site:

Centralized Dashboard

When you log in, you’ll be taken to your Dashboard immediately.  This is the one stop console that will let you access any of your active course subscriptions, review forum posts, download your files, and manage your billing and profile details.  We’ve worked hard to make this dashboard intuitive and easy to use as possible, and to make it look great on mobile as well.

Re-Designed Course Player

The course player is a completely custom built as well.  Of course, you’d expect to see your navigation menu on the left to get to your lessons, but we’ve also added a “Materials” fly out menu on the right where you can access files specific to any given lesson.The Materials flyout in action in the Skillwave Course Player

Community Forum Overhaul

We said is was a major redesign at Skillwave.Training, and we meant it.  One of our big goals here was to do a better job with the Skillwave help forum and foster a sense of community within it.  Our belief is that learning is great, but there can be another hurdle when trying to convert theory into practice with your own data.  We see the forum experience and Skillwave community as a crucial part of solving this issue, giving students the ability to:

  • Ask questions about the course materials,
  • Get help with applying techniques to their own data,
  • Interact with other people in the same training,
  • Practice applying their skills to other data sets, and
  • Reinforce their knowledge and help others in the process.

Any of our clients who have an active subscription to one of our paid products will find a completely revamped forum experience.  As forum posters ourselves, there were a couple of very important things that we wanted to make sure that our community was provided a good set of tools for:

  1. Asking To this end, we’ve made sure that we support topic tags, image and file uploads, code tags and a variety of rich formatting options.  (Our old forum was quite weak in this regard).
  2. Answering In addition to the tools above, we’ve added the ability to mark questions as solved. Our forums are searchable based on topic tags, answered status, solved status and more.
  3. Ensuring high quality answers. Our forum is private and monitored by our admin team.  Even if Matt, Miguel or myself aren’t the ones answering specific questions, we have a special “Recommended Answer” tag that we can apply to answers.  This serves two purposes to us: the first is providing assurance to the asker that they got a great answer, while the second is providing validation to a poster that they’ve provided a high-quality response.

Course to Question Integration

There’s one more really cool thing though… We also now give you the ability to post a forum question directly from a given lesson and provide links to all other questions that have been posted in this manner.  This serves both askers and answerers as it links directly back to the source of the question.  We’re super proud of this little feature and feel that it sets us apart from other platforms out there.  Not because other platforms don’t offer the ability to ask questions – they do.  But we serve all of that up right inside the lesson page.

A demo of the integration from course player and our forum

Check Out the major redesign at Skillwave.Training

If you haven’t checked out Skillwave.Training yet, you really should.  We’ve got all kinds of great courses related to Excel, Power BI, Power Query and DAX.  You can even try out the platform via our free Power Query Fundamentals course.  You won’t have access to the forums on the free tier, but you’ll be able to experience the rest of our new platform.

As we've just launched the site, we'd love to get your feedback.  For the next month or so, you can do that by clicking the little Feedback widget on the right side of any site page.  Let us know what you think!The feedback widget in action on Skillwave.Training

New Monkey Tools Features

We're super excited to let you know that we've just released some new Monkey Tools features!  Let's take a quick look as to what is new...

The Table Monkey

This feature was actually released back in December. However, since we announced it at the KSA meetup (which you can see on YouTube), we decided that it needed a personality of its own.  So now, on the Query Monkey menu you'll find the Table Monkey: a monkey who is dedicated to helping you build queries from Excel tables.The Table Monkey allows creating queries not just from one table, but multiple tables in one shot

Some of the cool features of this Monkey are:

  • It can create multiple "From Table" queries at once.
  • Tables can be excluded with a single click.
  • It can create "Staging" layers for you - as per our Dimensional Modeling course on Skillwave.Training, with custom staging layer names or counts.
  • You can rename the Excel tables by right clicking on the blue boxes that represent the Excel tables.
  • You can rename the Queries by right clicking on the green boxes that represent the data model tables.
  • It allows you to toggle the end query so you can load it to the data model or as a connection.
  • It provides a data typing algorithm that is smarter than Power Query's native algorithm.

Overall, we find this to be super useful. It allows us to create multiple table connections in a few seconds, rather than the minutes it would take us to set things up manually.

This feature is a Pro feature, but is fully functional in our free trial.

Create Query from M Code

The next feature that we included is a nice interface to create a new query from M code.  If you post in forums and need to quickly create a query for testing, you can simply take their code, paste it into the form, give it a name and click create.  Much easier than having to create a new query, edit the code, select everything and then paste:

Using the new Create Query from M Code feature to quickly create a new query

The main benefit of this form is saving you the headache of jumping into the query editor to create your query. Additionally, we also added the ability to indent the code right in the form. So if you're just trying to read it, it can be useful without ever creating a query at all.

We feel that this would be a super useful feature for those helping each other in the community. Thus, this feature falls in to our "Forever Free" category and works at all license levels (include after your trial expires).

Convenience Features - Pivots & Filters

Another one of the new Monkey Tools features that we've added is a Pivots & Filters menu to the Monkey Tools ribbon.  This is purely a convenience feature. It's designed to bring the commands closer to you so that you don't have to do as much tab switching:

The new Pivots & Filters menu allows creating PivotTables, PivotCharts and Slicers and Timelines without leaving the Monkey Tools ribbon

The version on the left is what we are terming the "Classic" view, which shows you the Insert PivotTable button (as well as PivotCharts, Slicers & Timelines).  The view on the right is what your menu will look like once the new Insert PivotTable button rolls out to your Office 365 install.  (If your Monkey Tools menu starts with PivotCharts, then head to our Options screen and uncheck the "Use Legacy PivotTable Menu Buttons" option.)

Bug Fixes

And - of course - like every release we do, we have included a bunch of bug fixes. Fixes that are applicable for all users including Pro, Trial and Free.

How to you get the new Monkey Tools features?

If you already have Monkey Tools installed, then head in to Monkey Tools -> Options.  If you are running 1.0.7678.28973, then you already have them.  And if not, click Check for Updates Now to update.

Don't have Monkey Tools installed?  You can try the full feature set for free for two weeks before the license reverts to a "free" license.  We think you'll be pleasantly surprised with how useful Monkey Tools is on a free license, and yet how much more it does in the Pro version.

 

More free features in Monkey Tools

Wow, it is hard to believe it is already December.  And looking back at my blog, I realized that I forgot to tell you that we released a few more free features in Monkey Tools over the past month!  In fact, November was a busy development month for us, so I though it would be a good time to share what we have done.

GetISOWeek Function

One of my friends saw the ability to create a calendar using the Calendar Monkey.  While he was suitably impressed, he did also ask me if it could do something he badly needed, which was to create a column displaying the ISO week that is commonly used in Europe.  Unfortunately, the Calendar Monkey had not learned enough about ISO weeks at that time, so was unable to help. So, we sent a couple of the Monkeys back to school…!

If you are on a trial or free version of Monkey Tools, you will find that the Query Monkey will now allow you to add a custom Power Query function called GetISOWeek to your file.  From there, you can manually call this function via the Invoke Custom Function button, or via writing a formula in the Custom Column dialog within Power Query.  Simply feed the function any date column to get the ISO Week Number, and include “true” for the final (optional) parameter if you prefer the “precise” text version:

Date\Formula =fnGetISO( [Date] ) =fnGetISO( [Date], true )
Sun 30 Dec 2007 52 2007-W52-7
Mon 31 Dec 2007 1 2008-W01-1
Tue 1 Jan 2008 1 2008-W01-2

Of course, adding a new function in to your workbook is great, but for our Pro users, the Calendar Monkey wanted to make it even easier, and added it as a default column choice.  No fuss, no mess, just choose the ISO date formats you need and let the Calendar Monkey do the rest!

The new ISO Week options displayed on the Calendar Monkey form

Measure Monkey – Basic Explicit Measures

While we are also super proud of our Measure Monkey who will help create Multiple Explicit Measures, we also realize that there are times where you need to create individual measures.  For this reason, we trained another Measure Monkey to do exactly that.

The new Basic Explicit Measures feature shown on the Measure Monkey menu

The Measure Monkey that focuses on Basic Explicit Measures provides you with a no-code experience to create… well… basic explicit aggregations.  (Yes, you could make Implicit versions via drag and drop, but serious modelers far prefer the more customizable and scalable explicit versions.)

This Measure Monkey will help you create these measures without writing a single line of DAX (although it does show you the DAX it has created.)  You will be provided a list of relevant aggregations (go home COUNTA!) and smart default formatting choices.  The Monkey will even capture your preferred defaults to make you even faster next time.

Side by side vide of creating a SUM and LASTDATE aggregation with the Basic Explicit Measure Monkey

And, like its brother who builds Multiple Explicit Measures, this Measure Monkey will work for you for free!

Support for Non-English Queries

Did I mention that my friend whom I referred to above, runs a French version of Excel?  Unfortunately, Monkey Tools had some challenges reading the queries in his model correctly.  While we have always claimed that we only support English versions of Excel, this still bothered us.

One interesting part about being a coder is that MOST coding is written in English. But every now and then, Microsoft localizes something that we did not expect.  So was the case with the underlying Power Query connection name.  To make a long story short, I have now learned that “Query” is “Requête” in French, “Abfrage” in German, and has other localized words among other languages.  And now that we know?  We have retrained our tool to deal with this challenge.

What this means to you if you are a user of a non-English version of Excel is – while we are not quite ready to say we fully support all non-English versions of Excel – we do believe Monkey Tools should work no matter the localization of your Excel install.  (We do still recommend caution here.  Until we say we OFFICIALLY support all languages, please do try the Trial version before you buy, and let us know if Monkey Tools has any issues reading your queries!)

Feedback Mechanisms

Another question we received from time to time was “How do I give you feedback?” or “How do I report a bug?”  It was enough that we realized that we had done a poor job of giving you a mechanism to do so.  So to that end, we have added the following to the Monkey Tools Help menu:

  • Log a Bug
  • QuerySleuth Indenter Issues (for issues specific to QuerySleuth indentation)
  • Feature Suggestions

Each takes you to a form that you can fill out to get in contact with the dev team.  And yes, we are open to hearing your suggestions!

Various Other Bug Fixes

Of course, no release would be complete without a few bug fixes.  There were a half dozen fixes that were included in the various November updates (plus another half dozen published last night.)  Each was minor, and not really worth mentioning on their own, but rest assured that we are trying to fix bugs whenever we find them.

What is the Current Version?

To make sure you have all of the current features, go to Monkey Tools -> Options.  If you are running a version that is less than 1.0.7640.41496, then click Check for Updates Now to update.

And if you don’t have Monkey Tools installed yet… what are you waiting for?  You can try the pro features for free for two weeks, and there are a ton of useful tools even if you don’t elect to purchase a pro license.  Click here to get your copy of Monkey Tools.  And hey… if you decide to upgrade to a Annual Pro license today, you can get 20% off with the code BF20MONKEYTOOLS.

So… What’s Next?

We are working on something cool that will help Excel modelers get started quickly.  And if you want to be one of the first to hear about it and see it in action you should attend the inaugural KSA Excel Power Platform meetup, as I’ll be demoing this new feature.

 

Monkey Tools Update Now Available

We’re pleased to announce the first Monkey Tools update is now available for download! This one contains a new feature, some new logic and an update to one of our data connectors. Read on for more information!

I can’t believe it’s been a month since our initial release, but here we are.  Since that release, a couple of notable things happened:

  • We published a blog post on PivotSleuth and Measure Tables. This was a learning experience for me, as I discovered something new about Measure Tables.  I always knew that you could mark your Fact tables as Measure tables by hiding all the unaggregated columns, but I didn’t realize that this also means that disconnected Measure tables will then suppress the “Relationships between tables may be needed error.”
  • One of my friends hit me with an interesting curve ball: he turned on “Store datasets using enhanced metadata format” in the Power BI preview features. And as it turned out, some of the methods we’d been using to analyze the Power BI model disappear when you do that.

Both of these have led to some improvements in the software, which we’re proud to say are finally available to you.

What’s new in version 1.0.7418.29970?

There are three major things that are new in this version:

A New Power BI Connector

This was actually a huge amount of work for us, as not only did we have to build a new connector to read the new Power BI file format, but we also had to analyze the file as it was opening to see if it was in the classic format or not.  And to make it harder, if you have the Enhanced Metadata Format turned on, a legacy file requires using our initial connector, but any refresh must be done with the new connector.  Fun times for a developer and, as you can imagine, it took as a bit of effort to pull it off.  It’s actually this piece that has held us back on the other features, as the connector MUST work and impacted everything.

While most of this work is invisible to the end user, there are two things that hope you do notice:

  • Connecting to the new file format is much faster than using the legacy format.
  • We also took the time to remove the reliance on configuring the version of Power BI that launches for you by default. We now just launch Power BI using your default connector, then bind to it, no matter how many versions you have on your machine, or which they are.

Updated Functionality in the PivotSleuth

As we learned in the PivotSleuth and Measure Tables blog post, hiding all unaggregated columns on a disconnected measure table flags the table as an official Measure table and suppresses the “Relationships between tables may be needed” message.  For that reason, PivotSleuth needed to recognize that this is acceptable.

In other words, when the Measures table is a properly formatted disconnected table, it needed to (and now does) show that there are no issues:

PivotSleuth giving a clean bill of health for a measures from a disconnected measure table

But when that disconnected Measures table has a visible column, not only should it have shown the issues, but also tell you what needs to be done to fix them.  And now it does:

PivotSleuth showing issues for measures used from an improperly formatted measure table

New QueryMonkey Feature: Add Measure Table

As mentioned before, our philosophy is “Build better, faster”.  For that reason we’ve added a new QueryMonkey feature for you:  Add Measure Table.

This feature will prompt you for a name for the table…

Prompting the user to enter a name for the new Measures table

And then create a new empty table in the data model for you:

An empty table in the data model

Unfortunately, the Excel team hasn’t given us a way to programmatically hide columns in the data model (we could REALLY use that ability), so we can’t take that last critical step for you:  Hiding the Measures column to prevent the "Relationships between tables may be needed" message.  But never fear, we do tell you exactly what needs to be done:

Advice from PivotSleuth on what to do to turn the new table into a proper measure table

So while we typically store our measures on the Fact tables, rather than a disconnected Measure table, we totally get that a lot of people like this approach.  Hopefully this make it a lot easier for you!

How do I get the Monkey Tools Update?

The answer to this depends on whether or not you’ve installed Monkey Tools yet.

If you haven’t, then head over to the Monkey Tools product page to download a 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…

Happy sleuthing! 🙂

Appending Columns with Different Names

Will Thompson from the Power BI team threw out a question on Twitter today related to Appending Columns with Different Names:

Let's say I have some CSVs coming in where the column names differ from file to file, but always in the same order. I could use PQ's automatic Column1/Column2 etc. names by skipping the header row and then have them all map to the same columns in AS.  Then build visuals on top of it that'll pick up the fixed column names from AS. Has anyone written a blog or tutorial that covers that sort of scenario? I want to point someone to some guidance…

No worries, Will, I’ve got your back. 🙂

Setting the stage

To be fair, the data source (CSV, AS, Excel or whatever), really isn’t relevant.  It’s all about the process of Appending Columns with Different Names.  What we need to recognize is that we need two types of tables here:

  • Data Tables: These tables have the data, but have different headers.
  • Header Table: This table has the correct headers for the data

And we also need to remember that the Data Table and Header Table columns are always in the same order.

Step 1:  Prep the “Data” tables

Preparing the data tables is pretty easy.  If the different column names are already showing in the headers like this:

A data table with headers

Demote and remove them by going to:

  • Home -> Use First Row as Headers -> Use Headers as First Row.
  • Remove Rows -> Remove Top Rows -> 1

Your table should now have headers called Column1, Column2, etc…

A data table with headers demoted into the first row

Do this for each table, then set each to be a staging query:

  • Excel: Load the query as a Connection Only query
  • PowerBI: Disable the Load of the query

Step 2: Prep the Header Table

Connect to your data, which might look like this:

A data table with headers

Clean it up by going to:

  • Home -> Use Headers as First Row -> Use First Row as Headers.
  • Keep Rows -> Keep Top Rows -> 1

You should now have a 1 row query that shows the names of the column headers.

A single row table with headers demoted to be the only row

It's time to save it:

  • Rename the Query as “Headers”
  • Set it to load as a staging query (as above)

Step 3: Combine the Data

This part is simple:

  • Right click the Header table -> Reference
  • Go to Home -> Append Tables -> Three or more tables
  • Add each of the data tables and click OK
  • Go to Use First Row as Headers
  • Set your data types

And you’re done:

A complete data table with the correct headers

Sample file (Excel) is available here.  But you can import it to Power BI if you prefer that look.

Monkey Tools is Here

We are super excited to announce that we’ve (at last) released the first version of our Monkey Tools software!  Ken has been working on this software on and off for the better part of 8 years now.  But after showing it to a friend in Wellington last year, we decided it was finally time to get serious.  We hired a full-time developer last summer and are finally ready to go live with the initial release!

What is Monkey Tools?

Monkey Tools is an Excel add-in (supported in Excel 2016 and higher) which provides tools for you - as a business intelligence author/reviewer - to:

  • Build models more rapidly
  • Follow recommended practices
  • Document your work
  • Audit files that you receive

It is targeted primarily at modelers and analysts who work primarily in Excel, but also push their models into Power BI.  (Our philosophy at Excelguru is to model in Excel first, then export to Power BI for reporting, sharing and security control.)

Oh, and super important… it installs on your system without requiring admin rights on your PC.  How cool is that?

What does Monkey Tools actually do?

Well… lots!  We’ve collected all the cool features under some themed buttons including:

  • QueryMonkey (for inserting new queries)
  • DestinationSleuth (to provide information on query load destinations)
  • QuerySleuth (helping understand your actual queries)
  • TimeSleuth (to benchmark query load times)
  • PivotSleuth (helping you diagnose Pivot Table field issues)
  • DAXSleuth (tools especially for working with DAX measures)
  • ModelSleuth (reporting on the properties of your queries and data model)

Cute names, right?  The Monkey builds things, and the Sleuths investigate things.  Here’s a high-level view of what they each contain.

QueryMonkey

Query Monkey gives you the ability to insert key queries like:

  • The famous “fnGetParameter” query and table (from Chapter 24 of M is for Data Monkey)
  • A “From Folder” setup that works with local and/or SharePoint hosted files
  • Dynamic calendar tables based on your data (for custom calendars, it even provides the option to insert the "periodicity" columns for Rob Collie's GFITW DAX pattern!)

The QueryMonkey provides a Dynamic Calendar generator

DestinationSleuth

Today, this is simply a viewer to visually indicate the load destinations of your tables (better than just “Connection Only” or “x Rows Loaded”).

The DestinationSleuth user form displays four different load destination types

QuerySleuth

This is a single form, packed with information and features such as:

  • A dependency/precedent tree view layout
  • Full colour display based on load destination
  • Colourful and indented M code
  • The ability to modify the M code and write it back to the editor WITHOUT LOCKING YOUR EXCEL User Interface!

The QuerySleuth shows a query dependency tree as well as indented and colourful M code

TimeSleuth

This feature allows you to time query execution in Excel, and even chart comparisons between them with or without privacy settings enabled.  If you’ve ever wondered which query is slowing down your workbook, or wanted to time test two different approaches, you may find this helpful!

A chart generated by Monkey Tools TimeSleuth user form

PivotSleuth

Have you ever seen that irritating “relationships may be needed” error when building a Power Pivot based Pivot Table, and wondered why?  Pivot Sleuth can tell you…

  • See the real, fully qualified names of the fields used in your Pivot Tables
  • Highlight potential or current issues in Pivot Table configurations
  • Debug cross filtering issues, “relationships may be needed” errors and errors where grand totals are returned for all rows on the Pivot Table

Debugging PivotTable errors with the PivotSleuth

DAXSleuth

We believe that measure dependencies are just as important as query dependencies, and this is the reason we build the DAXSleuth.  This form:

  • Displays a dependency/precedent treeview of your DAX measures
  • Provides a full colour display of Implicit and Explicit measures (with or without children), as well as Calculated Columns
  • Shows your DAX measures with colour highlighting in an indented format
  • Allows you to Indent, Un-Indent, Flatten, Duplicate and even Update measures without leaving the DAXSleuth
  • Exposes all locations a DAX Measure has been used (Pivot Tables, Pivot Charts, OLAP Formulae and Named Ranges), and even allows you to select those objects right from the DAX Sleuth!

Monkey Tools DAXSleuth user form in action

ModelSleuth

Have you ever had to provide documentation for your model?  Or picked up a model from someone else and had to review it?  The ModelSleuth provides reports and utilities such as:

  • A full model summary report showing key statistics about your tables, relationships, columns, measures and queries. (Trial and Free licenses are limited to every other record in this report.)
  • A model memory usage report, complete with how much memory is recoverable (for Excel based data models).
  • An unused columns report (for Excel based data models).
  • A DMV Explorer (for those who want to build their own reports).

Showing the impact of unused columns on memory via Monkey Tools ModelSleuth feature

Monkey Tools Supported File Types

The Monkey Tools add-in is compatible with Excel 2016 or higher, and can read from:

  • Excel files
  • Power BI Desktop files
  • Backup files (that you can export from the Monkey Tools software)

Will Monkey Tools get updates?

Oh yes, we have plans for many more features!

Our intended model is to deliver features (and bug fixes) as we develop them.  That means that there could be periods with no updates as we work on something big, or periods with multiple updates delivered in a single week.  We know that some people love frequent updates and some people don’t, so we let you control how often you get them:

Monkey Tools allows you to control update frequency

The key thing to recognize here is that we are not holding new features for a vNext. They’ll be delivered when they’re ready.

Can I try Monkey Tools before I buy it?

Ken did not become or remain a Microsoft MVP without contributing a large portion of tools and help to the community for free, and that won’t change.  Having said that, we’re paying a developer to work on this product full time and need to recoup those costs.  For that reason, we will always have both a Free version, as well as a Pro version.

Naturally, we want you to look at it, as we're convinced you'll like it.  And that's why we have a two-week trial that provides full access to almost all of the full feature set.  Once your trial expires, your license will automatically revert to a free license.  You’ll still get fixes and new features, they’ll just render in free mode (without colour, without field advice, etc.).  We do believe that you’ll still find the tool useful, just maybe not as useful without a Pro license.

Ready to learn more about pricing options and download the free trial?  Click here!

Unpivot Stacked Sets with Inconsistent Rows

I'm currently hanging out in New Zealand, with a friend who has generously let me stay at his place instead of a hotel.  What I didn't know when he offered a bed though, was that the cost of admission was a solution for a gnarly Power Query issue he was facing: How to unpivot stacked sets with inconsistent rows.

The data Jeff provided me with looked similar to this:

3 sets of data with products on rows and dates on columns

If it were only the first two tables that we were facing, this wouldn't be too difficult.  We cover unpivoting stacked data sets in both our Power Query Academy and our Power Query Recipes, whether they are single or multi column.  But the killer here is the third table... it has more rows than the first two.  So the question becomes how do we unpivot stacked sets with inconsistent rows?

Preparing the Data

Obviously the first piece we need to do is to get the data into Power Query and remove the irrelevant rows and columns.  The steps I went through were these:

  • Pull the data in to Power Query
  • Filter Column1 to remove null values
  • Remove the Total column

Once done, my data looked like this:

Stacked Pivoted Data Sets with 3, 3 and 4 rows

So now the data is nice and clean, but how to you unpivot it?

Separating the Data

The first trick is to find a way to separate this data into individual tables that represent each of the stacked data sets.  The good news here is that there is an indicator that we are looking at a new table.  Every time we see "Products" in Column 1, we know that's a header row and a new table will begin.  So we'll use this to separate the data into blocks, starting a new block each time we see that term.  To do this:

  • Go to Add Column --> Index Column --> From 1
  • Go to Add Column --> Conditional Column and configure it as follows:
    • Name:  Set
    • Formula:  if [Column1] = Products then [Index] else null

Shown below is the image view of the Conditional Column, as well as the results that it will create:

Building a Set column returning the Column1 if Column1 equals Products or null

As you can see, we've pulled out the number from the Index column if - and only if - the value in the first column is "Products".  The reason we want the null is that we can then:

  • Right click the [Set] column --> Fill Down
  • Select the [Index] column --> press DEL

You're now left with a nice table where the Set column shows a unique value for each data group:

Stacked Data with a "Set" column showing a unique value for each set

Grouping Into Tables

With an indicator for each group of data, we can now leverage this to separate the data into the individual data sets.  The method to do this is Grouping.

Select the Set column and then:

  • Go to Transform --> Group By and configure as follows:
    • Group by: Set
    • New Column Name: Stage1
    • Operation: All Rows

Grouping the Set column and adding an aggregation called Stage1 for All Rows

The data will then be grouped by the values in the Set column, and show the original data that was used to generate those groups.  Clicking in the whitespace beside the Table keyword will show each of these rows that were used in the grouping for that data point:

Results of the Grouped table, shown by clicking in the whitespace next to a group

Cleaning up the Grouped Tables

The challenge we have here is that we want to unpivot the data, but we've got some extra data here that will pollute the set: the values in the "Set" column which were added to allow the grouping.  We need to remove that.  To do so:

  • Go to Add Column --> Custom Column and configure it as follows:
    • Name:  Stage2
    • Formula: =Table.RemoveColumns( [Stage1], "Set"

Compare the results to that of the Stage1 column:

The Stage2 data table looks like the Stage1 data table, except the Set column has been removed

Before we can unpivot data, we need to promote that first row to headers... but we need to do it for each column.  No problem, we'll just break out another custom column:

  • Go to Add Column --> Custom Column and configure it as follows:
    • Name:  Stage3
    • Formula: =Table.PromoteHeaders( [Stage2], [PromoteAllScalars=true] )

Wait... what?  How do you figure that out?  I cheated.  I grabbed another table, promoted headers, then looked in the formula bar to figure out the syntax I needed.  The function name and table name were pretty obvious but unfortunately - even with intellisense - that final PromoteAllScalars part doesn't auto-complete.  Even worse, if you don't included it, it essentially just eats the top one row.  Once I had it correct, the results are exactly what I needed:

The Stage3 table now shows the headers promoted

As you can see in the image below, the Stage 3 table contains columns that have headers, as we wanted.  The 3rd table (carrying the identifier of Set 9), shows four rows, while the other tables show 3 rows.  So the data is now separated into tables, but they still have an inconsistent number of rows.

The Set1 group has 3 rows, and Set9 has 4 rows

Unpivot the Data

We have done everything we need to do in order Unpivot Stacked Sets with Inconsistent Rows.  We now just need to unpivot the data.  So let's do it:

  • Go to Add Column --> Custom Column and configure it as follows:
    • Name:  Stage4
    • Formula: =Table.UnpivotOtherColumns( [Stage3], {"Products"}, "Date", "Units" )

An indented version of the formula, as well as the results it produces, is shown here:

Displaying the Unpivot formula and the results for Set1

How do you learn to write this?  Click on one of tables to drill in to it, unpivot the single table, copy the code from the formula bar, then delete the temporary steps to back up.  You may need to do some tweaking, of course, but at least you can easily get the syntax that way.

Now that we have this, we can finish extracting the data:

  • Right click the Stage4 column --> Remove Other Columns
  • Click the Expand icon at the top of the Stage4 column
  • Set the data types
  • Load it to your destination

Sample File

If you'd like to download the sample file, you can do so here.