Splitting Text Strings with Power BI Text.SplitAny Function

Text.SplitAny is a Power Query function that allows you to split a text string into multiple parts based on one or more delimiters. This is useful when you have a text string that contains multiple values, such as a comma-separated list, and you need to extract each value individually.

Syntax

Syntax: Text.SplitAny(text as nullable text, separators as list, optional quoteStyle as nullable number) as list

The Text.SplitAny function takes three parameters:

• text: The text string to be split.
• separators: A list of one or more delimiter characters or strings that are used to split the text string.
• quoteStyle (optional): An optional parameter that specifies the quote style to use when the text string contains quoted values.

For example, let’s say you have a table in Power BI that contains a column called “Names” that looks like this:

List of Names

John, Mary, Tom 
Peter & Paul 
James; Lisa; Sarah

You can use Text.SplitAny to split the Names column into individual names by using a comma, semicolon, or ampersand as the delimiter. Here’s how you can do it:

1. Load the table into Power Query by selecting it and clicking on the “Transform data” button.
2. Select the “Names” column and click on the “Split Column” button.
3. In the “Split Column” dialog box, select “By Delimiter” and enter a comma, semicolon, or ampersand as the delimiter.
4. Click on “Advanced options” and select “Split into Rows” to split the column into multiple rows.
5. Click on “OK” to split the column.
6. Load the transformed data back into Power BI by clicking on “Close & Apply”.

The result will be a table that looks like this:

Names after applying Text.SplitAny function

John
Mary
Tom
Peter
Paul
James
Lisa
Sarah

As you can see, Text.SplitAny has split the Names column into individual names based on the delimiters you specified.

In addition to the delimiter characters or strings, you can also specify a quote style to use when the text string contains quoted values. This is useful when the delimiter character or string is used within the text string itself. For example, if the Names column contained a value like this:

“John, Mary”, Tom

You can use Text.SplitAny with the quote style parameter to split the value correctly:

Text.SplitAny(“””” & [Names] & “”””, {“,”, “;”}, QuoteStyle.Csv)

The result will be:

Result after applying more parameters

John, Mary Tom

In conclusion, Text.SplitAny is a powerful Power Query function that can help you split text strings into multiple parts based on delimiters. This function is particularly useful when you need to extract values from a text string that contains multiple values.

To learn more please visit Learn DAX