Improved Text.SplitAny – function for Power BI and Power Query

Today I’ll present an adjustment to the Text.SplitAny – function in Power BI’s query editor or Power Query. The native function takes a string as an input and splits the text by every character that is contained in the string. This seems fairly unusual to me and I haven’t used that function very often.

Problem

But what I have come across fairly often is the requirement to split a string by a bunch of different (whole) strings (instead of single characters).

Solution

Read more

Return all occurrences of text between delimiters in Power BI and Power Query

The M-function Text.BetweenDelimiters does what it says and returns the text between 2 delimiters in the query editor of Power BI and Power Query. But irrespective of its optional arguments you can use to select specific occurrences, it will always only return one string found.

Problem

How to retrieve all occurrences of strings between in a text field like here for example: I’d like to get a list that contains “Python” and “R”:

“Meanwhile I prefer +Python!+ to +R!+”

Meaning all strings found between the delimiters “+” and “!+”.

Solution

This function will return the desired list:

Read more

Transform text with exceptions in Power BI and Power Query

Recently I picked up an interesting request to transform text with exceptions: Transform the words in a column to proper case, but keep certain keywords like in a defined list.

Problem: Transform text with exeptions

Say you have a list with specific terms that shouldn’t be proper cased like so:

And you want to proper case the following column:

Convert to proper case with exceptions

So I proper case each word that is not contained in the “KeepTable”, identify the elements in the “KeepTable” in a case insensitive way and transform them into the syntax that’s specified in the “KeepTable”.

Solution

Read more