Load script & transformations

Qlik load script statements and prefixes (ApplyMap, Peek, Resident, Join, CrossTable, IntervalMatch, Store, AutoNumber, loops, Hierarchy…) translated to Power Query M or Fabric.

ApplyMap('MapName', expr [, default]) Mapping Load and ApplyMap ApplyMap looks up a value in a two-column mapping table during load. In Power BI, use a merge in Power Query, a record lookup, or simply a relationship with RELATED. The behaviour differs for case sensitivity, duplicates and missing keys. Partial / different approach Peek(field_name [, row_no [, table_name]]) Peek (running balances in the script) Peek reads a field from a row already loaded into the output table. It is used for running balances and for reading values into variables. Power Query has no row-by-row state, so use grouped list logic, a SQL window function upstream, or a DAX measure. Partial / different approach Previous(expr) Previous (compare with the previous input row) Previous returns an expression's value from the previous input record. Power BI has no LAG in Power Query, so use the index-shift self-merge pattern, a SQL LAG upstream, or a DAX calculated column that finds the previous value. Partial / different approach RecNo(), RowNo() RecNo and RowNo RecNo numbers input records and RowNo numbers output rows. In Power Query, add an index column before the filter step (RecNo) or after it (RowNo). The model has no row order, so keep the index as a column. Partial / different approach LOAD ... RESIDENT table; DROP TABLE table; Resident load and intermediate tables A Resident load reads a table already in memory. The Power Query equivalent is a referenced query, but it re-runs its source instead of reading from memory. Heavy multi-step transformations are better done upstream in a warehouse, dataflow or lakehouse. 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 [Inner|Left|Right|Outer] Join / Keep Join and Keep Qlik joins on all fields with the same name, and a plain Join is a full outer join. Power Query merges need explicit key columns and a join kind. Keep filters a table without merging, which maps to a semi-join filter or an inner merge you don't expand. Partial / different approach WHERE Exists(field [, expr]) Where Exists / Not Exists Exists() checks whether a value has already been loaded into a field anywhere in the script. In Power Query, filter against a buffered list from another query, or use an inner or anti merge. Qlik's "everything loaded so far" behaviour is order-dependent, and Power Query has nothing like it. Partial / different approach LOAD ..., Sum(x) RESIDENT t GROUP BY ... Group By in the load script GROUP BY in a Qlik load pre-aggregates a table. Power Query's Group by (Table.Group) is the direct equivalent. In Power BI you often don't need it at all, because measures aggregate at query time. 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 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 IntervalMatch(value) LOAD start, end ... IntervalMatch IntervalMatch links values (dates or numbers) to the intervals that contain them. Power BI relationships only match equal values. Do it in Power Query, in a DAX calculated column, or by expanding intervals into one row per day. Partial / different approach LOAD * INLINE [ ... ]; Inline load (hard-coded tables) Inline loads define small tables in the script. In Power BI, use Enter data in the ribbon, a #table expression in Power Query, or DATATABLE in DAX. Direct equivalent STORE Orders INTO [lib://Data/Orders.qvd] (qvd); STORE INTO (writing QVDs) Power Query in Power BI Desktop can't write files. A query only feeds the model. Qlik's STORE pattern, transforming once, saving a QVD and reusing it, maps to Fabric: a Dataflow Gen2 with a data destination such as a Lakehouse table, a notebook writing Delta tables, or a Power BI dataflow that other models connect to. Without Fabric or Premium, the honest answer is to store in your database or SQL views. Partial / different approach AutoNumber(CustomerID & '|' & Region) AS %CustKey AutoNumber and surrogate keys Qlik developers use AutoNumber() to replace long text or composite keys with compact integers, which saves RAM and speeds up associations. In Power BI you rarely need it. VertiPaq dictionary-encodes every column, and relationships work on text keys. When you need a single-column key because a relationship can't use two columns, build a dimension with Table.AddIndexColumn and merge it into the fact. Partial / different approach Hash128(CustomerID, OrderDate) AS RowHash Hash128 and Hash256 No 1:1 equivalent in Power Query or DAX. Neither has a hash function. Qlik developers use Hash128 and Hash256 for change detection in incremental loads, for anonymising IDs, or for stable composite keys. Do this upstream: HASHBYTES in SQL Server, Azure SQL or Fabric Warehouse, or sha2() in a Fabric notebook or Spark. No 1:1 equivalent FOR EACH vFile IN FileList('lib://Data/*.csv') ... NEXT FOR ... NEXT and FOR EACH loops Power Query has no loop statement. It's a functional language. Loop over a list instead. List.Transform covers "do this for each item". List.Generate covers while-style loops. A custom function invoked per row of a table covers "load each file/table/month". Looping over files is built in as the Folder connector with Combine files. Partial / different approach MapSubString('Map_Abbrev', Address) MapSubString MapSubString replaces every occurrence of each "from" string in a mapping table with its "to" string. Power Query has no single function for this, but List.Accumulate over the mapping rows with Text.Replace does the same. DAX would need one nested SUBSTITUTE per pair, so do it in Power Query. Partial / different approach LOAD *, Year(Date) AS Year; SQL SELECT ...; Preceding LOAD A preceding LOAD lets one LOAD read the output of the LOAD or SELECT below it, without a resident table. Power Query works like this by default. Every applied step takes the previous step as input. The main thing to watch is query folding: early steps that fold run in the database, like the SQL SELECT at the bottom of a preceding-load stack. Direct equivalent Hierarchy(NodeID, ParentID, NodeName) LOAD ... Hierarchy and HierarchyBelongsTo Qlik's Hierarchy prefix flattens a parent-child table into level columns, and HierarchyBelongsTo builds an ancestor-to-descendant table. In Power BI, the standard pattern is DAX PATH functions in calculated columns: PATH() builds the chain, PATHITEM() extracts Level 1, Level 2 and so on, and PATHCONTAINS() answers "belongs to" questions, for example in RLS. Partial / different approach SET ErrorMode = 0; ... IF ScriptError > 0 THEN ... ErrorMode and ScriptError SET ErrorMode = 0 makes the Qlik script carry on after an error, and ScriptError lets you branch on it. Power Query handles errors at the value or step level. try ... otherwise replaces one failing expression, and Replace, Remove or Keep errors work on columns. A query with an unhandled error fails the whole refresh. There is no "continue the rest of the script" mode. To orchestrate around failures, use a Fabric pipeline. Partial / different approach QUALIFY *; UNQUALIFY CustomerID; QUALIFY and UNQUALIFY QUALIFY prefixes field names with the table name so Qlik doesn't associate tables on shared names. Power BI doesn't associate tables on shared names at query time. Relationships are model objects, and the same column name can exist in several tables. Desktop's "Autodetect new relationships" option (Options > Data Load) can create relationships from matching names at load, so check the Model view or turn it off. Most QUALIFY statements can simply be dropped. If you want prefixed names, use Table.PrefixColumns. Partial / different approach DROP TABLE Temp; RENAME FIELD Cust TO Customer; DROP and RENAME (tables and fields) Dropping a temporary table in Qlik maps to a staging query with Enable load unchecked. It feeds other queries but doesn't reach the model. DROP FIELD maps to Table.RemoveColumns, and RENAME FIELD to Table.RenameColumns. Qlik's "RENAME FIELDS USING map" maps to Table.RenameColumns with a list built from a mapping table. Direct equivalent LOAD *, FileName() AS Source FROM [lib://Data/sales_*.csv] (txt); Loading many files (wildcards and FileName) Qlik loads every matching file with a wildcard in the FROM path, auto-concatenates them, and FileName() tells you which file each row came from. Power Query's Folder or SharePoint folder connector with Combine files does the same. It adds a Source.Name column, which is the FileName() equivalent. Filter on the file name to get the sales_*.csv pattern. Direct equivalent LOAD DISTINCT CustomerID, Region RESIDENT Orders; LOAD DISTINCT LOAD DISTINCT maps to Power Query's Remove duplicates (Table.Distinct) on the selected columns. Two differences matter. Table.Distinct is case-sensitive while the model isn't, and Remove duplicates on a subset of columns keeps the first row per key. In DAX, DISTINCT() and VALUES() return unique values for calculated tables. Direct equivalent SUB LoadDim(vName) ... END SUB; CALL LoadDim('Customer'); SUB, CALL and $(Include) A SUB in Qlik maps to a custom function in Power Query, (param) => ..., invoked like any function. $(Include=...), which shares script files across apps, has no direct equivalent in Power BI Desktop. The closest options are shared dataflows, which centralise the transformation itself, Power Query templates (.pqt), or copy-pasting functions, which is honest but manual. Partial / different approach Window(Sum(Sales), CustomerID) AS CustomerTotal Window() in the load script Qlik's script Window() function adds a partitioned aggregate to each row without collapsing rows, like SQL SUM() OVER (PARTITION BY ...). Power Query has no OVER clause. Use Group By, then merge back, or Group By with an "All rows" step. In most models, the better answer is a measure computed at query time. Use a calculated column only when you really need the value stored per row. Partial / different approach