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…

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!

One hell of a weekend!

This is a personal post, but it was a HUGE weekend for me where some very cool things happened.  If you’re a fan of Excelguru on Facebook, follow me on Twitter, or are connected to me on LinkedIn, you’ve already seen a bit of this news, but this is a more detailed version of things.  Smile

It started with a soccer game…

We started bright and early, hopping into the car at 7:30 on Saturday morning to head down to Victoria.  Excelguru sponsors the Angry Jellybeans girls soccer team, of which I’m the coach, and we were off to play in the Gorge Soccer Association’s mini jamboree.  Three games over two days, and a good test for our Nanaimo based team to see how they’d fare against the Victoria clubs.  We pulled in shortly after 9:00 and played our first game at 10:00.  The girls came out flying, earning a 6-1 victory in their first match.  Confidence was high, the sun was shining, and it was turning into a great day.

We had some time to kill before our second match at 2:00, so we all hung out in the sun for a while, had a hotdog, and just enjoyed the day.  I hung out with my daughter for a while, just chatting.  I don’t remember most of what we talked about, but I do remember this… “Daddy, why do you have to go to an awards presentation tonight?  I want you to stay with us for the team dinner.”  It’s the kind of thing that kills you, you know?

As a bit of background here, my boss had nominated me for Vancouver Island’s Top 20 Under 40 awards.  The award recognizes the top 20 business and community leaders under 40 years of age on Vancouver Island.  I’d been shortlisted to the top 100, but the awards banquet was Saturday night in Courtenay… about a 3 hour drive from Victoria where we were playing.  Fortunately I’ve got a great co-coach in my friend Scott.  Scott agreed to look after the team dinner, my in-laws got a hotel room and kept my daughter overnight, and Deanna and I were set to head up to Courtenay immediately following the game to make it in time for dinner.

At any rate, what can you do to a question like that?  I looked at her and said “well, sweetie… if someone wants to honour you with an award, you should do everything you can to show up.  It’s only polite.”

She nodded, not happily mind you, but she accepted it.  Then came a question from behind her:  “Coach Ken, what award are you going to win?”

The question to them wasn’t IF I’d win, it was WHAT I’d win.  Smile

The funny part is that I hadn’t really told a lot of people about this at all.  I’d been shortlisted to the top 100, but I was actively trying to convince myself that I would be okay with that, even if I didn’t get recognized in the top 20.  That’s a hard battle to wage with yourself when you are as competitive as I am.  Really hard.  I don’t settle for short of the mark.  The award said Top 20… to me, that was the mark.  If I’d never been nominated, I wouldn’t have lost a minute of sleep over it… but I was… and that meant the prize was top 20.  And yet, I saw the bios of the other finalists, and they were incredible.

I laughed it off, and pretty soon it came time to square off in the next match so we were back to the pitch.

The second game… well… it didn’t start so well.  The girls didn’t play nearly as well as they should at all, and we were losing 0-1 at half time.  Scott and I pulled them in, had a little chat about how they were playing and how we knew they could play.  It was pretty cool, actually… we coached and they listened… and the second half was a different story.  The girls turned it on, scoring three, conceding one, and ending up with a 3-2 come from behind victory.  We were 2-0-0 on the day.

Equally cool though, was what happened on the sidelines.  We play 6v6 (including a keeper).  Since I keep my keeper in net the entire half, that meant I had 5 girls on the field to sub on and off, and 6 on the sidelines.  We’d taken to subbing 3 at a time.  If it was a corporate environment you would have called it a succession plan… and the effect was interesting.  6 of the girls decided that they should do some cheering… Who’s going to win?  Not the King, not the Queen, it’s the Angry JellyBeans!  3 girls went on the field, 3 came off.  And the three who were still on the sidelines pulled in the new arrivals… a new round of cheering started.  Then 3 went on and 3 came off… and the cheering started again... and again… and again…

It was very cute.  The cool part was this though… our opponents started a cheer of their own… and their coach was wide eyed.  “I’ve never heard these girls cheer before… wow!”  My Angry Jellybeans inspired the other team to do something they’d never done before.  Pretty awesome.  Smile

Game done, and with a 2-0-0 record, Deanna and “Coach Ken” hopped in the car, hoping to go 3-0-0 on the day.

The Top 20 awards

We pulled in to the Old House at about 6:10, and I hopped out of the car still dressed in soccer pants and my Angry Jellybeans hoodie.  Working my way through the tuxedos and evening gowns I reached the front desk.  The clerk asks “Are you checking in”.  I nodded, and he pulls the last folio off the counter behind him.  “You must be Ken… you need to change!”

I laughed, but he was right.  We bolted to the room, changed superman style, and were out the door about 10 minutes later.

After a quick drive to the Filberg Centre, we parked, hoofed it up the stairs and… holy crap!  There was a red carpet rolled out for us… wasn’t expecting that!  I also wasn’t expecting to be given a glass of champagne as I walked in the door either… made me kind of feel like a movie star!

Because of the late arrival from the soccer tournament, we missed most of the mix and mingle time, but we still had time to chat with Russell (Fairwinds Asset Manager), his wife Mary, Jim (my boss) and Julie (Jim’s wife) who had all come out to support me.  A couple of drinks, some chatting about the day, and a little conversation with a couple of nice guys that rounded out our table of 8 made for a good wind down time from the crazy drive we’d just experienced to get there.

They also gave us a nice full colour book of all the nominees and their bios. We started flipping through them… which only helped to make me more nervous!

Next up were a few speeches including one from Premier Christy Clark, who had skipped her son’s hockey game to come and talk to the finalists before the awards.  She had a great speech that had the crowd laughing. Not political, which was nice, but rather focused on us.  You can see some of that in the Chek News coverage.  Next came dinner, then they got to the awards.

Each of the top 100 finalists was given a nice framed certificate to hang in their office.  Here’s a picture of Deanna and I (smartphone shot, so quality not great), with mine:

Top100

And then came the announcement that they were going to start announcing the Top 20… Russell slams his hand on the table “A hundred bucks on Ken!”

So here’s all this support around me, all this encouragement, all this faith… and me still trying to convince myself that it’s okay if I don’t make the Top 20.

And then it started to roll out, Oscars style.  They called Troy Wilson.  As he’s working his way to the stage they’re reading a more complete bio.  Troy makes his acceptance speech, and they call another name.  The process repeats and they call another, and another… and another…

And I’m telling myself “It’s okay.  Top 100 of all under 40’s on the Island is awesome.”

… and another, and another…

“You don’t have to WIN everything you know…”

… and another …

“It’s a hell of an achievement, be proud!”

… and “KEN PULS!”…

The words that came out of my mouth at that moment were deep and profoundly inspired…. “Holy Shit.”

I won.

I can actually say that I was a bit in shock.  But I managed to get up there and put together a speech on the fly that I’m pretty happy with.  You can see it here:

And then something remarkably cool happened.  As I was heading back to my table to show off the trophy a young guy stops me… I can’t remember the exact words, but it went something like this:

“Ken, I just wanted to tell you that I took one of your Excel courses a couple of years ago.  You really inspired me, and I decided to pursue my accounting designation.  I’m now enrolled and pursuing my CMA.  I’m a huge fan of your website and spend a lot of time there too.  I just wanted to say thank you.”

To me, that is the ultimate compliment.  I was so stunned I never even got his name, but wow.  That was inspiring.  Smile (If you read this, drop me a line!)

Then back to soccer…

After the awards were all presented we returned to the room to get some sleep, turning in around 12:30 AM.  The alarm was supposed to go off at 6:00 AM (which felt like 5:00 AM thanks to the daylight savings time change that night.)  Thankfully Dee woke up right at 6:00 as neither my phone nor hers actually went off.  45 minutes later we were back on the road heading back to Victoria for the final Angry Jellybeans game vs the Gorge FC host club.

When the girls found out that I had won they all surrounded me and gave me a great big Angry Jellybeans cheer, which was really touching.  We then had a chat about our goals for the final game, which ended up with the girls deciding that they were going to “win it for Coach Ken".

My girls are a talented team, and they play hard.  But what I saw on the field on Sunday was remarkable.  They did everything that Scott and I had coached them to do all season long.  They spread out, they were aggressive, they run into open space, they passed to each other, they connected and they scored… and scored… and scored…  At 6-0 Scott and I were just about to implement our “3 passes before shooting rule” when they scored again.  So in the rule went… and they scored again… and again…  By half time it was 9-0.

We came back on after the half with the girls very clear that they had to connect 5 passes before they were allowed to shoot on net.  It was actually great.  There is nothing more amusing that seeing the look on a parents face when one of your players is standing in front of an open net, and they turn and pass the ball because they’ve only completed 3 passes of the required 5. The Gorge girls also spread out and got more aggressive.  Because of that they did manage to score a goal as well, which was great.  The final score ended up with an 11-1 win for us.

Again though, my girls led a 1 hour cheering session through the game.  And just after half time Gorge responded.  Out of nowhere I hear one of my players say “they just cheered us back… come on, let’s have a cheer off!”  And sure as anything they did.  They’d cheer, wait for the other side to respond, and launch into a follow up… over and over again.  It was such a weird counter-point… we were creaming them on the field, and they were still having FUN.  And that is just plain awesome.

My only regret the whole weekend is that I let my girls run the score up as high as they did before putting in the passing rules.  I never want to beat a team by 10 goals, as that is disheartening for anyone.  I want these girls, no matter the team, to play soccer year after year.  But at the end of the day I can’t help by feel that this time the girls on the other side had fun anyway.  They were all smiles when they shook our hands, and the coaches from the other side thanked me for my actions in stopping the slaughter while still letting them play.  It really felt like a win-win.

I can’t begin to say how proud I am of my Angry Jellybeans.  They played amazing, and they inspired a bunch of kids to have a great time and have fun in ways that their coaches haven’t seen before.  It was an inspiring end to a great tournament, and a great weekend.

photo

And in the end…

On Saturday night I became part of a very exclusive club… one I’m proud, honoured, humbled, and flattered to be a part of. (As this was the first year of the event, there are only 20 members to date.) What’s amazing is that I never pursued this, I just did what I do. With my Excel stuff it’s what I love to do, with my other stuff what I feel that I need to do, but regardless, I was recognized for just being me, and that is pretty cool.

And on Sunday I saw the culmination of something very cool in my amazing Angry Jellybeans.  Not only the wins, but the fact that they inspired others in ways that seems like it could only come through some kind of magic.

To all of those of you have liked my Facebook statuses, LinkedIn updates, tweeted me and expressed congratulations on my award through any other manner, Thank You. It means a great deal to me.  I truly feel like a lucky man right now.  Smile