How to analyse M-functions step-by-step?

This is a quick tutorial on how to analyse M-functions step-by-step.

Queries, that have been transformed into a function (1) can be invoked in the query-editor (2) and they will then return their output for you to check (3). But they will not show you how they did it. In the query-editor where you normally see the single steps and their individual output if you click on them, you will just see one line for the function and one for the invocation (4):

Step1

So how can we make them reveal all their secrets for us to see the quickest way?

Yes, we could edit the code and replace every parameter that has been passed. But this can get a bit tedious, especially if you haven’t written the code by yourself and don’t know where and how often the parameters are used. A very quick and safe way is to replicate the parameters within the let-expression by copy-&pasting like this:

AnalyzeM-Functions

That way you just define them once and don’t have to care about how often they are used within the following query. And it is very quick to do as you can just copy the parameter-expression and comma-separate your inputs.

Step2

I bet you want to check this out on Chris Webb’s latest blogpost here: DynamicDateSelections.zip

Enjoy & stay queryious 🙂

Multiple replacements or translations in Power BI and Power Query

A common task when cleaning data is to perform multiple replacements at once. When dealing with “translation tables” you might have come across Chris Webb’s solution using List.Generate to deal with this. This works fine – but might deliver unexpected results if you don’t consider it’s implications properly. Say we add lines like shown below, then we might expect the result as highlighted in yellow:

Image2

Multiple replacements using 2 different techniques

So hey – what happened here?: Our catamaran turns out as a bearamaran and the chair turns to chwater. Read more