QlikToDax

Mid(Code, Index(Code, '-') + 1)

Left, Right, Mid, Index and Len in Power BI

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 DAX Power Query

Short answer: In DAX, MID(T[Code], SEARCH("-", T[Code], 1, 0) + 1, LEN(T[Code])). When there is no dash this returns the whole code, as Qlik does (Index returns 0, so Mid starts at 1). In M, use Text.AfterDelimiter([Code], "-"), with a Text.Contains guard if codes without a dash must come back whole.

What it does in Qlik

  • Left(s, n), Right(s, n) and Mid(s, start [, n]) are 1-based.
  • Index(s, sub [, count]) returns the 1-based position of the nth occurrence, counting from the end if count is negative, or 0 if not found.
  • Len(s) returns the length.

Qlik example

Qlik script and chart
Left(PostCode, 4)
Mid(Code, Index(Code, '-') + 1)      // after the first dash
Mid(Path, Index(Path, '/', -1) + 1)   // after the last slash
Len(Trim(Comment)) > 0

Power BI equivalent

DAX

DAX
PC4 = LEFT ( T[PostCode], 4 )
After dash =
MID ( T[Code], SEARCH ( "-", T[Code], 1, 0 ) + 1, LEN ( T[Code] ) )
// no dash: SEARCH returns 0, so MID starts at 1 and returns the whole code, as in Qlik

Power Query

Power Query M
= Text.Start([PostCode], 4)
= if Text.Contains([Code], "-") then Text.AfterDelimiter([Code], "-") else [Code]   // after first dash
= Text.AfterDelimiter([Path], "/", {0, RelativePosition.FromEnd})  // after last slash
= Text.PositionOf([Code], "-")      // zero-based, -1 if not found
= Text.Length(Text.Trim([Comment])) > 0

Gotchas and behaviour differences

  • DAX MID needs the length argument. Pass LEN(...) to mean "to the end". FIND and SEARCH raise an error when the text isn't found unless you supply the 4th argument, the not-found value.
  • Without the guard, Text.AfterDelimiter doesn't return the whole text when the delimiter is missing, while Qlik's Mid(s, Index(s, '-') + 1) does. Guard the after-last-slash case the same way if some paths have no slash.
  • Text.Range errors when the offset is past the end of the string, while Text.Middle just returns less text. Prefer Text.Middle and Text.Start for messy data.

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 TextBetween(Code, '[', ']') TextBetween 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 Trim(Replace(Name, ' ', ' ')) Replace and Trim Qlik Replace maps to DAX SUBSTITUTE, not DAX REPLACE, which is positional. Trim has a subtle difference. Qlik Trim and Power Query Text.Trim only remove leading and trailing spaces. DAX TRIM also collapses repeated spaces inside the text to one, so the results can differ. 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.