Fireside Chat With a Data Monkey

Tomorrow, at 2:00PM Pacific time, I'll be hosting "Fireside Chat with a Data Monkey".  This event will be a social chat, where I'll be answering any and all of your questions about our new book Master Your Data with Excel and Power BI (the newly updated second edition of M is for Data Monkey).

Cover of Master Your Data

Maybe you're interested in knowing:

  • Why this book isn't just called 'M is for Data Monkey v2"?
  • Where the title 'M is for Data Monkey' came from?
  • How Miguel and I determined what was going to be included?
  • Why it took so darned long to get finished?
  • When hard copies will be available?
  • How you can get started writing your own book?
  • Something else entirely!

Overall, Fireside Chat with a Data Monkey is intended to be a relaxed and social discussion around writing and publishing a technical book on an ever changing technology.  It's a place for me to connect with you, and just chat.

In preparation for the chat, I'd like to invite you to ask the questions you'd like me to answer, which you can do via the Fireside Chat survey. This session is intended to answer YOUR questions, so please feel free to submit any (and all) questions your have, including items from the list above (if they are of interest to you.)

In addition, if you'd like to join the webcast, why not add it to your calendar now so that you don't miss it!

We put a great deal of thought and work into writing Master Your Data with Excel and Power BI. I'm very much looking forward to chatting with you all, and sharing some of the experiences that went into making writing it.  So grab a beer and/or a coffee, and I hope to see you there!

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.

Building a SelectQuery Function

For a while now, I have been wanting to have the capability in Power Query to select a query by name PROGRAMMATICALLY.  Why?  Building a SelectQuery Function would allow me to execute one of multiple “Transform” queries depending on a user selection on the Excel sheet. This will help me process log files from multiple vendors which each have different contents and field names.

Here is a fairly simple example with only three input queries (although my true setup actually has seven potential queries to select from:

Illustration of the query chain with three queries that pull from a single data source, and another query that feeds the SelectQuery function to choose which to execute

How the setup is intended to work:

I have a named range called “User_Select” on my sheet that has a Data Validation dropdown with the names of my source queries:

And what I want to do is read the value from the User_Select named range into my query named Selection_Query. This provides a scalar value that matches the query I’d like to execute.

The Issues

  • I do not want to land the multiple input queries on sheets (too much data involved).
  • I am not a fan of Power Query Parameters for this approach, as they must be changed from within the Power Query user interface (I don’t really want my users going in there.)
  • I do not want to use “brute force” – I want to do this programmatically so that it is easy to maintain in future.

What is the “brute force” method?

The brute force method is essentially coding a great big IF/THEN statement that contains each possible query.  Looking at the M code, you’d end up with something like:

let
Source = if SELECTION_Query = "Query 1" then #"Query 1" else
if SELECTION_Query = "Query 2" then #"Query 2" else
if SELECTION_Query = "Query 3" then #"Query 3"
// (and so on, and so on, and shoobie-doobie-doo ?)
else null
in
Source

The problem however is that each time I create a new “Transform” for a new vendor, or retire it from production, I would also need to come back and update my brute force query to reflect these changes.  It would be MUCH simpler if I only had to add/remove the query name from my drop-down list, and not worry about messing with the M code of my Selector query.

Some hope for building a SelectQuery function

On 19 Feb, Gasper Kamensek presented a session at VANPUG’s Power BI track that got me excited. In his presentation, he showed how to programmatically select from some LANDED queries using the Excel.CurrentWorkbook() statement in Power Query:

let
Source = Excel.CurrentWorkbook({[Name=Selection_Query]}[Content]
in
Source

Enter Expression.Evaluate

Now, that worked great for items that had been landed to a worksheet table and got me thinking about this some more.  The challenge I’ve been facing is that I need to select from queries which were NOT landed to a worksheet table and therefore don’t show up via the Excel.CurrentWorkbook() function.  Wondering if this was even possible, I asked my friend Ken Puls. And guess what he Puls-ed out of his bag of tricks?

Source = Expression.Evaluate("some text string", #shared)

Now, I had encountered Expression.Evaluate() in the Power Query M function reference, but it was not clear to me what it was intended to do. But after Ken and I bashed this back and forth a bit… WOW!  Does this ever have potential!

Ken explained that Expression.Evaluate() works very similarly to Excel’s INDIRECT() function - it takes an input and tries to evaluate it at run-time.  Unlike Excel, which seems to just evaluate the provided term against any and all Excel items, Expression.Evaluate() requires you to specify the library you want to use to interpret the code.  And that’s where the #shared parameter comes in, as this parameter provides a list of not only all Power Queries in the solution, but also all of the available Power Query functions.

So Ken’s suggestion was to pass the name of the query I wanted in to the Expression.Evaluate function, and evaluate it against the shared library. At that point – he told me – it should give me the results of that query.

Armed with this theory, I was eager to plug it in to my SELECTOR query, which gave me this:

let
Source = Expression.Evaluate(Selection_Query, #shared)
in
Source

AND IT DIDN’T WORK. ?

Expression.Identifier to the Rescue!

Turns out, it’s not Ken’s fault – I like to name my queries with spaces and leading numbers.  After a little digging, it became apparent that the Expression.Evaluate() needed me to refer to “Query 1” with a pound sign and quotes.

In other words, this DOESN’T work:

=Expression.Evaluate(Query 1, #shared)

But this DOES:

=Expression.Evaluate(#"Query 1", #shared)

So now I just needed to figure out how to automatically “escape” the query with the #” “ requirement where necessary.  I suppose I could have put those into my Excel drop down, but that would make the list values look kind of ugly, so I went hunting something a bit more elegant.

After poking around in the M manual, I found Expression.Identifier(“some text”), and guess what it does? It converts the name we see in the Queries & Connections panel into the correct “# and quote” syntax.

So that gives me:

let
qName = Expression.Identifier(Selection Query),
Source = Expression.Evaluate(qName, #shared)
in
Source

AND IT WORKS!

Completing the Solution

To make this as flexible as possible (and allow me to use it in other projects), I decided that building a SelectQuery function was the way to go.  So here’s what I ended up with:

The fxSelectQuery function:

(qName) =>
let
Source = Expression.Evaluate( Expression.Identifier(qName) ,#shared)
in
Source

And at that point, I can invoke whatever query I need by passing the results of the Selection_Query to the fxSelectQuery function like this:

The Output Query:

let
Source = fxSelectQuery(#"Selection_Query")
in
Source

And the end result is that I select the query I want to run from an Excel data validation list, click update, and I’m done.  How cool is that?

One caveat that I should probably mention here is that you must disable the formula firewall in order to use this setup.  You can do this by going to Get Data -> Query Options -> Current Workbook -> Privacy ->  Ignore.

You can download the example file here if you’d like to see the results of building a SelectQuery function in action.

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.

 

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…

Creating Power Query Based Calendars

We’re super excited to announce that we have given the Monkey Tools calendar creator feature an upgrade.  In fact, it is so much of a power up, that this feature has graduated into its own full grown monkey!  We call it the Calendar Monkey.

In our initial version, the Calendar Creator would create the queries necessary to load a single column calendar of unique dates into the data model and add PeriodID columns for 364-day calendars like 445 and 13 fiscal periods.

The Calendar Monkey adds a couple of pretty powerful features to this original mix.  Let’s walk through the experience quickly to see what I mean.

Step 1:  Define the Calendar Boundaries

Step 1 screen of the Calendar Monkey, allowing you to choose your calendar type, the calendar start/end boundaries and year end

Not much has really changed here.  You can still:

  • Pick your calendar type (12 month, 13 month, 445, 454, 544), and define your custom year end (including a different month for 12 month calendars.)
  • Define a name for your calendar table query
  • Choose the load destination
  • Pick any valid date columns for the Start and End date of your data

These settings allow our monkey to build the calendar to dynamically span the entire range of your data on every refresh.

The only real difference here is that we’ve added a checkbox and a Next button.  (Notice that Create is still available, if you just want to accept the monkey’s default choices for the rest of the options you’re about to see.)

Step 2:  Choose Calendar Columns

One of the things that always bothered us about our original version is that it created the calendar’s Date column, but then left it up to you to add the different date formats that you wanted.  So we decided to improve that, as you can see here:

Step 2 of the Calendar Monkey screen provides a series of check boxes allowing you to specify which date format columns you'd like to add to your model

Our monkey pre-selects the most common date formats, but if you ever check/uncheck one, it will learn your preferred defaults and provide those next time you go to inject a calendar.  The columns shown dynamically react to your choices in Step 1 as well… if you use a Dec 31 year end for a 12 month calendar, you only need the first two columns – so that’s what the Calendar Monkey will show you.  If you have a custom year end (like Jun 30 or Sep 30), you may also want Fiscal columns, so the monkey provides those as options too.  And if you work with a 364-day calendar like a 445 variant or a 13 fiscal periods calendar… there is a final column of PeriodID’s that shows up in that blank spot too.

Step 3: Adding Relationships

Depending on your choices in Step 1, the Calendar Monkey will determine if you will be presented with this page or if it will be skipped.  If you choose to load your calendar to the Data Model (or Data Model & Worksheet), the monkey will list every date column loaded to the data model.  The purpose of this is simple; let you decide if your new calendar table should be linked to any of those columns listed.

For any columns you check, Calendar Monkey will do its best to create those relationships after loading your table to the data model.  (There are some things that can prevent the monkey from accomplishing this, such as creating an inactive relationship.)

Step 3 of the Calendar Monkey provides checkboxes for each date column in the data model, allowing you to declare which tables you'd like to relate your calendar table to.

Step 4: Creating the Calendar

Even though the Calendar Monkey has a lot of work to do when you click create, it also knows the value of good feedback.  For this reason, it will update you as to the progress as it completes all the individual tasks, as you can see here:

This screen shows feedback from the Calendar Monkey, letting you know what has been done, as well as what you need to do need.

Now unfortunately, there are a couple of things that the monkey is unable to do (thanks to a lack of security clearance with the Excel data model).  Rather than just ignore these essential tasks, however, it will tell you what needs to be done, with the exact steps to do so.

As a bit of a pro-tip here… you don’t actually need to close the Calendar Monkey window to take action on those steps… so keep it open until you’ve hopped into the data model and made the advised changes!  Of course, if you understand what’s happening, and know the steps you’ll need to take, there is also an option to automatically close the summary screen upon completion as well.

Looking at the data model with the new calendar table already related to the other tables

What the Calendar Monkey cannot do

There are three things that the Calendar Monkey can’t do at this time:

  1. Automatically hide the keys on the “many” side of the relationship (the foreign keys)
  2. Automatically create the sorting hierarchies to sort Month Name by Month Number and Day Name by Weekday Number
  3. Create the calendar in a Power BI file

Honestly, while we joked earlier that the Calendar Monkey doesn’t have security clearance, the reality is that there is a limitation with the data model’s extensibility model which is preventing us from solving the first two items.  The third… its on our backlog.

So how do you get the new Calendar Monkey?

You need Monkey Tools version 1.0.7493.29574 or higher, and you'll have the Calendar Monkey ready to do your bidding.

If you haven’t already, 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…

Name Worksheets After Queries

Have you ever loaded a Power Query to a worksheet and then changed the name to match the query? It's a shame that there is no option to name worksheets after queries, as this would be handy.

Well, after seeing this request come up in the forums last week, we thought that this would be a great feature to add to the DestinationSleuth in Monkey Tools.  So as of build 1.0.7433.38066... it's done!

How to Name Worksheets After Queries

We wish that we could add this as an option in the Close & Load dialog, but sadly that's not possible.  So we did the next best thing...  Once you've loaded your queries, you simply need to open our DestinationSleuth and:

  1. Select the queries you're after
  2. Click the Rename Sheets button

Using DestinationSleuth to select queries and change the names of their host worksheets

At that point, we'll quickly loop through the host worksheets and rename them to match the query landed to that sheet.

Name Worksheets After Queries While Changing Load Destinations

You might also notice a new checkbox called "Name Sheets After Queries".  This checkbox allows you to name worksheets after queries while changing a load destination to create a new table.  It's also super easy to use:

  1. Select the query (or queries) you wish to change
  2. Choose to change the load destination to a Table
  3. Check the Name Sheets After Queries checkbox
  4. Click the Update Load Destinations button

Using DestinationSleuth to change a load destination from Connection Only to Table, and update the worksheet to the Query name at the same time.

We'll change the Load Destinations, creating the worksheets AND naming them to match the query in one step.

This is also a "Forever Free" Feature

As mentioned in my last post, while the DestinationSleuth's colour highlighting is only available in the trial and pro versions of Monkey Tools, the ability to Change Multiple Load Destinations at Once is a "forever free" feature.  And so is the ability to rename worksheets after queries!  All you need is Monkey Tools version 1.0.7433.38066 or higher, and you'll have that ability at your disposal.

If you haven’t already, 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…