QlikToDax

Date(Date#(text, 'YYYYMMDD'), 'DD-MM-YYYY')

Date#() parsing and Date() formatting in Power BI

Date#() interprets text as a date and Date() formats a number as a date, keeping it a dual. In Power BI, parse in Power Query with Date.FromText and set the display format on the column. DAX FORMAT() turns a date into text.

Partial / different approach Power Query DAX

Short answer: Parse with Date.FromText([Text], [Format = "yyyyMMdd"]), or "Change type > Using locale". Format in the model with the column's format string. Note the case of the format codes: Qlik uses YYYY-MM-DD, Power Query and DAX use yyyy-MM-dd.

What it does in Qlik

Date#(text, format) (an interpretation function) reads text into a dual (number plus original text). Date(number, format) (a formatting function) changes only how the number is shown. The common combination is Date(Date#(Field, 'YYYYMMDD')). Timestamp# and Timestamp do the same for date-times.

Qlik example

Qlik script
LOAD Date(Date#(OrderDateText, 'YYYYMMDD'), 'DD-MM-YYYY') AS OrderDate,
     Timestamp#(CreatedText, 'YYYY-MM-DD hh:mm:ss')        AS Created
FROM [lib://Data/raw.csv] (txt, utf8, embedded labels, delimiter is ',');

Power BI equivalent

Parse in Power Query

Power Query M
= Table.AddColumn(Source, "OrderDate", each Date.FromText([OrderDateText], [Format = "yyyyMMdd"]), type date)
= Table.AddColumn(Previous, "Created", each DateTime.FromText([CreatedText], [Format = "yyyy-MM-dd HH:mm:ss"]), type datetime)
// or for regional text such as 31/01/2024:
= Table.TransformColumnTypes(Source, {{"OrderDateText", type date}}, "nl-NL")

Format for display

Select the column in the model and set Column tools > Format (for example dd-MM-yyyy or a custom string; lower-case mm is minutes). The value stays a date. Use DAX FORMAT only for labels, because it returns text.

DAX
Order date label = FORMAT ( Orders[OrderDate], "dd-MM-yyyy" )

Gotchas and behaviour differences

  • Format codes differ in case. Qlik: YYYY MM DD hh mm ss. Power Query and DAX: yyyy MM dd HH mm ss. In both, MM is month and mm is minutes, but DD is not a day code in Power Query, and hh there is the 12-hour clock.
  • Changing the type without a culture uses the locale of the file or the machine. A refresh in the service can then parse 01/02 differently. Always specify the format or the culture.
  • Qlik keeps the original text in the dual. Power BI keeps only the value, so the original string formatting is lost after parsing.

Official documentation

Related pages

Dual('Jan', 1) Dual values Qlik fields can hold a dual value: a text and a number at the same time. Power BI columns have one data type, so you use a text column sorted by a number column (Sort by column). Partial / different approach MonthStart(date [, period_no]) MonthStart, MonthEnd and period boundaries MonthStart and MonthEnd return the first or last millisecond of a month. Use Date.StartOfMonth and Date.EndOfMonth in Power Query, or EOMONTH in DAX. Note that STARTOFMONTH in DAX is a table function, not the same thing. 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.