QlikToDax

SubField(FullName, ' ', 1)

SubField in Power BI

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

Short answer: Do this in Power Query. Use Text.Split([FullName], " "){0}, which is zero-based. To turn a list into rows, use Split column > By delimiter > Advanced > Split into Rows. If it has to be DAX, use PATHITEM(SUBSTITUTE(T[s], ",", "|"), 2).

What it does in Qlik

  • SubField(text, delimiter [, n]): n is 1-based. A negative n counts from the end. Qlik's help shows that a NULL input or an empty part returns an empty string. It doesn't document what an n beyond the last part returns, so test with Len(...) = 0 rather than IsNull(...).
  • Without n, in a LOAD, Qlik generates a row for each substring. People often use this for multi-value fields such as tags.

Qlik example

Qlik script
LOAD *, SubField(FullName, ' ', 1)  AS FirstName,
        SubField(FullName, ' ', -1) AS LastName;
// one row per tag
Tags: LOAD OrderID, SubField(TagList, ',') AS Tag RESIDENT Orders;

Power BI equivalent

Power Query

Power Query M
= Table.AddColumn(Src, "FirstName", each Text.Split([FullName], " "){0}, type text)
= Table.AddColumn(Src, "LastName",  each List.Last(Text.Split([FullName], " ")), type text)
// 3rd part, null when missing (Qlik gives an empty string for a NULL input)
= Table.AddColumn(Src, "Part3", each Text.Split([Code], "-"){2}?, type text)

// one row per tag (what the UI 'Split into Rows' generates)
= Table.ExpandListColumn(
    Table.TransformColumns(Orders, {{"TagList", each List.Transform(Text.Split(_, ","), Text.Trim)}}),
    "TagList")

DAX (calculated column, if you must)

DAX
Part 2 = PATHITEM ( SUBSTITUTE ( T[Code], "-", "|" ), 2, TEXT )
Last part = PATHITEMREVERSE ( SUBSTITUTE ( T[Code], "-", "|" ), 1, TEXT )

Gotchas and behaviour differences

  • Power Query lists are zero-based, so {0} is SubField's 1. The ? after {2} returns null instead of an error when the part doesn't exist.
  • PATHITEM treats | as the separator. If the text can already contain |, pick another approach.
  • Splitting into rows multiplies the rows of that table. Keep the tags in their own table (OrderID, Tag) linked to the orders, just as Qlik does with a separate table.

Official documentation

Related pages

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 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 CrossTable(attribute, data [, n]) CrossTable (unpivot) CrossTable turns a wide table (for example one column per month) into attribute/value rows. Power Query's Unpivot other columns does the same. Both drop null cells, so row counts usually match. Direct equivalent Concat(DISTINCT Product, ', ') Concat (string aggregation) Concat() joins values into one string. DAX has CONCATENATEX and Power Query has Text.Combine. The sort order and the duplicate handling are different from Qlik's defaults. Direct equivalent

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.