QlikToDax

CrossTable(attribute, data [, n])

CrossTable (unpivot) in Power BI

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

Short answer: Select the qualifier columns (the first n in Qlik) and choose Unpivot other columns, or use Table.UnpivotOtherColumns(T, {"Region"}, "Month", "Sales").

What it does in Qlik

CrossTable(Month, Sales, 1) LOAD ... keeps the first n (default 1) columns as qualifiers. Every other column becomes a row, with the column header in Month and the cell in Sales.

Qlik example

Qlik script
Budget:
CrossTable(Month, Sales, 1)
LOAD Region, Jan, Feb, Mar, Apr FROM [lib://Data/budget.xlsx] (ooxml, embedded labels, table is Sheet1);

Power BI equivalent

Power Query

Power Query M
let
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Region"}, "Month", "Sales"),
    Typed     = Table.TransformColumnTypes(Unpivoted, {{"Sales", type number}})
in
    Typed

Gotchas and behaviour differences

  • Nulls: Qlik's CrossTable doesn't create rows for null cells, and Power Query's unpivot also drops nulls, so the two usually agree. If you need a row for every empty cell, in Qlik add NullAsValue *; SET NullValue = 0; before the load, and in Power Query replace nulls (with 0 or "") before you unpivot. Empty strings "" are not null in Power Query, so they're kept as rows.
  • New columns: both Unpivot other columns and Unpivot columns from the ribbon pick up a new month column on the next refresh, because Power Query records them as Table.UnpivotOtherColumns with the fixed qualifier columns. Only Unpivot only selected columns (Table.Unpivot with an explicit list) ignores new columns. Qlik's CrossTable takes every column after the first n, like Unpivot other columns.
  • The attribute is text ("Jan"). Map it to a real date or month number to relate it to the date table.

Official documentation

Related pages

Generic LOAD key, attribute, value Generic load (pivot attribute/value data) A Generic load turns key/attribute/value rows into one table per attribute, linked by key. In Power BI you usually pivot to a single wide table with Table.Pivot. It errors on duplicate attribute values unless you give it an aggregation. Partial / different approach Concatenate [(table)] LOAD ... Concatenate (append tables) Concatenate appends rows to an existing table. Qlik also does this automatically when field sets match. Power Query's Append queries (Table.Combine) matches columns by name and fills missing ones with null. Direct equivalent LOAD ... AUTOGENERATE ... WHILE ... IterNo() Master calendar (date table) The Qlik master calendar script maps to a date table built in Power Query (List.Dates) or DAX (CALENDAR), marked as a date table and related to the fact date. It is essential for every time-intelligence function. 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.