Blog

In the last episode of I Have the Power, we looked at calling REST APIs via Custom Columns using M’s Web.Contents function. Today, we’re going to look at creating Functions in Power Query.

Functionalisation

As a reminder, we have so far translated text by calling Google’s translation API via a Custom Column. However, we might find that we have many queries in our dataset that need some kind of translation; perhaps one query that is all in one language and needs to be in another, or one like ours above where we have a different set of input languages. Instead of creating Custom Columns for each where we copy and paste our M code, making changes as appropriate, we can instead create a Power Query function to meet our needs.

Functions are mappings of inputs to outputs; in our case, we want to create a mapping of translation text, source language, and destination language to some output translated text. By defining a function, we can re-use the M code wherever and whenever we need to translate some text.

We’re going to pick up exactly where we left off last time, and the quickest way to start creating a Power Query function is to create a new blank query and open the advanced editor.

We can use the M code that we’ve created for our custom columns as a skeleton to build the function on. We want to define three inputs, inputText, sourceLanguage and destinationLanguage, which we can then simply concatenate into our URL using ampersands:

Image

This then creates a custom function (called Query1, but we can rename it something sensible) that we can call via the Invoke Custom Function icon on the Add Columns tab.

This will open the following dialog:

Image

The important thing to note here is that I can choose what each input variable should be, depending on the context in which I am using the function; if all of my inputText was the same language, I could enter that language’s ISO code as text here instead of a reference to a column – similarly, for destinationLanguage, I could enter an ISO code in that field too.

However, I want to use the parameter I defined earlier, DestinationLanguage. If you try, you’ll find that all you can enter is some free text or a column reference. There’s no obvious way to reference a parameter here. If you do as above and type in ‘DestinationLanguage’, hoping that Power Query will know you meant the parameter, you’ll find that the translation does not quite work. If we open the advanced editor, we’ll see exactly why:

Image

Power Query has inferred that you meant to enter a string for destination language (as evidenced by the quotes around it) that so it is asking the APU to translate the text to some language called “DestinationLanguage” which doesn’t quite work!

There is a way that we can change that string into a reference to the parameter, but we have to use the advanced editor to do so. All we have to do is remove the quotation marks around DestinationLangauge so that it turns from a string to a reference to our existing parameter:

Image

And now we have called our function on some new data in a new query:

Image

So what?

Functions are a really useful part of Power Query; by creating Functions, we can give ourselves a toolbox filled with little bits of M code that we can use wherever and whenever we need them. Above, we made a function that translates some input text that we can use in cases where we have a variety of input or output languages or just a single input or output language. Potentially, this saves us loads of time; instead of having to re-write or copy and paste our code, we can simply call our function. Whenever you find yourself copying and pasting your M code, consider whether a Function might just work for you.

If you’re interested in learning more about Power Query (especially in the context of Power BI), we cover both fundamental and more advanced aspects of Power Query in our Power BI Fundamentals and Power BI Advanced courses – or if you have a specific Power Query question you’d like answered, please reach out either via the comments on this blog post, or email to info@burningsuit.co.uk.