QlikToDax

TextBetween(Code, '[', ']')

TextBetween in Power BI

Power Query's Text.BetweenDelimiters is a direct match, and the Extract > Text Between Delimiters menu generates it. Two differences: the occurrence index is zero-based, and Qlik returns NULL when a delimiter is missing. Add a guard if you depend on that NULL. In DAX, combine MID and SEARCH.

Direct equivalent Power Query DAX

Short answer: Use Text.BetweenDelimiters([Code], "[", "]"). For Qlik's 2nd occurrence, pass index 1. Add an if Text.Contains(...) guard if you need NULL when no brackets exist.

What it does in Qlik

TextBetween(text, delimiter1, delimiter2 [, n]) returns the text between the nth occurrence of delimiter1 and the following delimiter2. It returns NULL if either delimiter isn't found.

Qlik example

Qlik script and chart
TextBetween('<abc><de>', '<', '>')      // abc
TextBetween('<abc><de>', '<', '>', 2)   // de
TextBetween(Comment, '[', ']') AS Ticket

Power BI equivalent

Power Query

Power Query M
= Table.AddColumn(Src, "Ticket", each
    if Text.Contains([Comment], "[") and Text.Contains([Comment], "]")
    then Text.BetweenDelimiters([Comment], "[", "]")
    else null, type text)

// second occurrence: zero-based index 1
= Text.BetweenDelimiters("<abc><de>", "<", ">", 1, 0)   // "de"

DAX (calculated column)

DAX
Ticket =
VAR s = T[Comment]
VAR a = SEARCH ( "[", s, 1, 0 )
VAR b = IF ( a > 0, SEARCH ( "]", s, a + 1, 0 ), 0 )
RETURN IF ( a > 0 && b > 0, MID ( s, a + 1, b - a - 1 ) )

Gotchas and behaviour differences

  • DAX SEARCH is case-insensitive and supports ? and * wildcards, so escape them with ~ if they're your delimiters. FIND is the case-sensitive version. Both return an error when nothing is found unless you pass the 4th argument.

Official documentation

Related pages

SubField(FullName, ' ', 1) SubField SubField has two modes. With an index (SubField(s, ',', 2)) it returns one part of the string. Without an index, in a LOAD, it creates one row per part. In Power Query, use Text.Split for the first and "Split column by delimiter into rows" for the second. DAX has no split function, but PATHITEM with SUBSTITUTE returns the nth part. Partial / different approach Mid(Code, Index(Code, '-') + 1) Left, Right, Mid, Index and Len DAX has the same 1-based functions: LEFT, RIGHT, MID, LEN, plus FIND or SEARCH in place of Index. In Power Query the functions are zero-based. Text.PositionOf returns -1 when nothing is found, where Qlik's Index returns 0, and Text.Middle starts at offset 0. Direct equivalent KeepChar(Phone, '0123456789') KeepChar and PurgeChar In Power Query, Text.Select keeps only the listed characters (KeepChar) and Text.Remove drops them (PurgeChar). Both accept ranges such as {"0".."9"}. DAX has no equivalent. You would need one nested SUBSTITUTE per character, so clean the text in Power Query. Partial / different approach

Spotted an error or a better pattern? QlikToDax is maintained by a working BI consultant; corrections are welcome via the About page. Last reviewed 2026-10-25.