Monkey Tools

Add Measure Table

Article Overview

Many modelers like to store their measures on a single “Measures” table in the data model.  If you’re one of them, you may appreciate the ability to quickly add a disconnected measure table into your data model for this purpose.  Thanks to the Measure Table Monkey, this is quite easy!

In this article, we’ll explore exactly what this Monkey does, as well as how to use it.

Working with the Measure Table Monkey

What this Monkey does

The purpose of this Monkey is to create a query which returns a blank table, and load it to the Data model. There are two things that you will need to do to make this happen:

  1. Provide a name for the table
  2. Hide the Measure column in the data model
Let’s walk through this process now…
Click Query Monkeys -> Measure Table Monkey to add measure table

Step 1: Calling the Measure Table Monkey

To create a new disconnected Measure table:

  • Go to the Monkey Tools ribbon tab
  • Choose Query Monkeys -> Measure Table Monkey
  • Optionally, enter a name for your table
  • Click OK

At this point, Monkey Tools will check if you already have a table with the name you provided.  It is worth noting that – if you do – clicking OK will only replace the Power Query associated with the table.  In other words, your existing measures will not be deleted!

Unfortunately, in order to create a proper measure table for your model, there is one other thing left to do, and it’s something that we are unable to automate for you as Microsoft hasn’t provided us a way to do so.  The good news is that Monkey Tools tells you the exact steps needed:

Step 2: Finalizing the Measure table

We highly recommend that you follow this next step, as it will prevent Excel from showing a “Relationships may be needed” error whenever you use a measure stored on this table.  It’s not difficult, you just need to:

  • Go to Monkey Tools -> Manage Data Model
  • Optionally, switch to Diagram View (top right)
  • Locate and select the new table
  • Right click the Measure column -> Hide from Client Tools
Exit Power Pivot. At that point, your measure table is ready for use!

Share:

Facebook
Twitter
LinkedIn

Related Posts

SCD2 Monkey

Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time – until we get

Calendar Monkey

Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time – until we get

Table Monkey

Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time – until we get

SmartFolder Monkey

Article Overview One of our favourite tricks is to create a “From Folder” solution which avoids hard-coding a file path, instead pulling it dynamically via

Create Query from M Code

Article Overview One of the things that we do at Monkey Tools is help people with questions in forums. When someone provides us with an

Copyright Excelguru Consulting Inc.