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:

Read more

Extract pattern string and numbers from text using List.Accumulate in Power Query

A typical task when cleaning data is to extract substrings from string that follow a certain pattern. In this post I’m going to describe a method that uses the List.Accumulate function for it. Extract a pattern string.


I have to extract a payroll key from a description field that starts with 8 number, followed by an “-” and another number.

aölsfdk0125-fds  da12345678-0asdf

So I’m after the 12345678-0.


I plan to approach this by

Read more