Transform a query into a function in Power Query and Power BI

In my previous blogpost I’ve described a method how to extract a substring that follows a certain pattern from a string. In this post I show how to transform a query into a function that can be applied to many rows of a table.

Video how to transform a query into a function

Please check the video for detailed steps. In there I also show how to modify the code. It shall also detect strings with a sequence of just 8 numbers. In the original query, those had to be followed by a minus sign and another number:

The steps in pictures

Copy the hardcoded string that shall be replaced by a function parameter.

Create a new parameter.

Paste copied string as as a default parameter in into the “Current Value” field.

Replace hardcoded string in query by a reference to the parameter.

Check query, rightclick mouse and choose “Create Function”.

Name the function. This will transform the query into a function.

Then add a column by invoking the function.

As you might have recognized, a new folder will be created where the original query and the function parameter(s) are collected. Also, you’ll find the newly created function in it.

The function is “connected” to the original query. That means that all changes that I make in the original query will automatically be transferred to the function as well. This makes adjusting the function or troubleshooting it so much easier.

Adjustment

The original request I got was to make sure that also 8-digit-number strings without the trailing “-x” shall be found as well. Therefore I made an adjustment in row 16. There it will be checked 8th position of the so far collected string is reached. If so, store it.

Sample file

You can download the file with the code here:  RegexDummy_Part2.zip

Also, you can find the code the “functionized” version on my GitHub page.

Enjoy & stay queryious 😉