Monkey Tools

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 M code sample, it has always been a bit painful to get it set up: Create a new blank query, go to the Advanced Editor, paste in their code, save the query as connection only, and then go back to start looking at in in detail.

This Monkey was designed to make this process more efficient, and will be most helpful to forum posters. We use it to quickly take a user’s code and:

  • Indent it for readability
  • Create a new query so that you can view it in QuerySleuth or edit it via the Power Query editor
In this article we will explore how it works so that you can spend your time helping your users, rather than mucking about with the advanced editor.

Working with the Create Query from M Code Monkey

Basic Use Case

As you would probably expect, creating a new query is fairly easy:

  1. It starts by launching the Create Query from M Code Monkey.  Once you do, you’ll find that the name of the New Query is already highlighted. Just start typing to overwrite it with something better.
  2. Once you’ve picked a new query name, hit the tab key, and the cursor will move immediately to the M Code Formula box. Press CTRL + A, CTRL + V and it will paste in your code for you.
  3. Press tab again, and you’ll be taken to the Indent button. Click it or press Enter, and the Query Monkey will indent it for you right away. (Believe it or not, we actually use this feature without even creating the query on occasion.  It is just so much easier to read indented code to see where things may be going on.)
  4. Press tab one more time, and your focus will be taken to the Create button. Just click the button (or press Enter), and the Monkey will create your query for you based on the defaults you have set in the form.
What happens next depends on how your options are configured, but if you are using the defaults, the form will get cleared and send you back to Step 1 so that you can create multiple queries.
The Create Query From M Code Monkey and the tab order to move through fields efficiently. (1 = Name, 2 = Formula, 3 = Indent, 4 = Create)
The tab order of the Create Query from M Code Monkey
The Create Query from M Code Monkey containing some un-indented code copied from a forum post.
Code copied from a forum post
Code indented with the Create Query from M Code Monkey
Forum Post Code Indented and Beautified

Understanding the Create Query From M Code Options

This Monkey also contains a few different options for you to customize your experience to make it work best for the way you need it. Each of these is described at right and – as an added bonus – records your preference so that it will be correct the next time you ask the Monkey for help!

This section will allow you to record the load destination for the given query you are creating:

  • Connection Only
  • Data Model
  • Table (new worksheet)

Remember that your choice will be saved for the next time you call on this Monkey!

The settings in this section include:

  1. Font Size:  Set your preferred size for the text in the formula window.
  2. Highlight Syntax Upon Indent: When you indent your M code, it will also color the terms to make it more readable. If you prefer to avoid this, just uncheck this option.
  3. Clear Form After Creation. When checked, clicking the Create button will create the query, empty the formula window and return you to the Name field so that you can create another query.
  4. Close for after creation. This option changes the behaviour of the form to close immediately after creating a query, saving you a click.

By default, this Monkey will alert you and ask you what to do if you try to create a query with the same name as an existing query. Your options here include changing the default to:

  • Replace the query (silently),
  • Prompt you as to what action to take, or
  • Skip the query (silently), taking no action.

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

Click Query Monkeys -> Measure Table Monkey to add measure table

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

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

Copyright Excelguru Consulting Inc.