Floor(OrderTimestamp) AS OrderDate

Splitting timestamps into date and time in Power BI

Qlik developers split timestamps with Floor() for the date and Frac() for the time, partly to save memory. Do the same in Power Query with DateTime.Date and DateTime.Time. It makes the date join to the calendar table and reduces column cardinality in VertiPaq, so the reason is the same in both tools.

Direct equivalent Power Query

Short answer: In Power Query, add a Date column (DateTime.Date([Timestamp])) and a Time column (DateTime.Time). Round the time to the minute if seconds don't matter, then remove the original timestamp.

What it does in Qlik

Floor(ts) drops the time part, keeping the number of days.
Frac(ts) keeps only the time fraction.
Date(...), Time(...) and Timestamp(...) only format the value. They don't remove the time part, which is a common mistake.

Qlik example

Qlik script

Date(Floor(OrderTS))              AS OrderDate,
Time(Round(Frac(OrderTS), 1/1440)) AS OrderTime   // nearest minute

Power BI equivalent

Power Query

Power Query M

= Table.AddColumn(Src, "OrderDate", each DateTime.Date([OrderTS]), type date)
= Table.AddColumn(Src, "OrderTime", each
    let t = DateTime.Time([OrderTS]) in #time(Time.Hour(t), Time.Minute(t), 0), type time)
// then remove OrderTS if you don't need it

DAX (only if you can't change the query)

DAX

Order Date = DATE ( YEAR ( T[OrderTS] ), MONTH ( T[OrderTS] ), DAY ( T[OrderTS] ) )

Gotchas and behaviour differences

Changing a column's data type to Date in Power Query truncates the time, like Floor. Changing only the format in the model, to a short date, still stores the full timestamp, and the relationship to the calendar won't match. That's the Power BI version of Qlik's Date()-without-Floor() bug.
A datetime column with seconds can have millions of distinct values. Splitting it shrinks the model in Power BI for the same reason it does in Qlik.

Official documentation

Floor - script and chart function (Qlik Help)
Frac - script and chart function (Qlik Help)
DateTime.Date (Power Query M) (Microsoft Learn)
DateTime.Time (Power Query M) (Microsoft Learn)
Optimization guide for Power BI (data reduction) (Microsoft Learn)

Date(Date#(text, 'YYYYMMDD'), 'DD-MM-YYYY') Date#() parsing and Date() formatting 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 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 Round(Sales, 0.01) Round, Floor and Ceil Qlik's Round(x, step, offset) rounds to a multiple of a step such as 0.01 or 5, not to a number of decimal places. DAX ROUND takes digits and MROUND takes a multiple. Watch the tie-breaking: Qlik rounds halves up. DAX ROUND rounds away from zero. Power Query Number.Round uses banker's rounding (round half to even) by default. 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.