Downloads
Power Query MDate and timeIntermediate

DateTime.FixedLocalNow

Returns the current local date and time as one fixed value for the whole evaluation of a query.

Syntax
DateTime.FixedLocalNow() as datetime

Returns: The local datetime at the moment the query started, used the same way on every row and step.

What it does

DateTime.FixedLocalNow returns the current date and time, but holds that value steady for the whole refresh. DateTime.LocalNow can return a slightly different time on each row or step, so a column built with it can show different timestamps for rows that were processed in one run. Use the fixed version when every row should carry the same run time, such as an export log or a batch stamp.

Syntax and arguments

Example

A data load stamps every row with the time the load ran, so the team can tell which refresh a row came from when a figure is questioned later.

let
    RunAt = DateTime.FixedLocalNow(),
    Lines = #table(type table [Account = text, Amount = number],
        {{"1000", 250}, {"4010", -120}, {"5100", 75}}),
    Stamped = Table.AddColumn(Lines, "LoadedAt", each RunAt, type datetime),
    Labeled = Table.AddColumn(Stamped, "LoadedLabel", each DateTime.ToText([LoadedAt], "yyyy-MM-dd HH:mm"), type text)
in
    Labeled

Every row gets the same LoadedAt value, which is the time the query started. The label column shows the same date and time to the minute on each row.

Example: a second, different use

Use the fixed time as the report date in the page header, so the header and the table agree with each other.

Things to watch

In business terms

Auditors and controllers ask when a set of numbers was pulled. A fixed load time on every row answers that question, and it lets two reports from the same run be matched to the same moment.

Related functions

All Power Query M functions · View as Markdown · M basics