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 😉
Fantastic Blog post on how to transform a query into a function that can be applied to many rows of a table! This post was really helpful and I loved reading it. Thank you very much and keep sharing such informative articles.
Thanks for this kind feedback 🙂
I am new to Power BI, seeing this conversion of query to function was great. It shows I have a lot to learn. Thank you for sharing !!!
Awesome piece of code.
Would it be too difficult to modify the function to find multiple occurrences of the pattern in the string?
Thank you so much for this – it suits what I needed perfectly. Appreciate you sharing your skills and knowledge.
I’ve used this before without issue, but when I tried it today I got an error message:
Formula.Firewall: Query ‘ImportTXFile’ (step ‘Source’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination
Any ideas anyone?