DateTime.FromFileTime

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function DateTime.FromFileTime

Understanding File Time

Before discussing the M code behind the DateTime.FromFileTime function, it’s crucial to understand what file time is. A file time is a 64-bit value that represents the number of 100-nanosecond intervals since January 1, 1601 (UTC). It’s a Windows-based timestamp used to keep track of file and folder creation, access, and modification times.

The DateTime.FromFileTime function converts this file time value into a DateTime value, which is a standard format for date and time values.

The Syntax for DateTime.FromFileTime

The syntax for the DateTime.FromFileTime function is straightforward. The function takes the file time value as a parameter and returns the corresponding DateTime value.

DateTime.FromFileTime(fileTime as number) as datetime

The `fileTime` parameter is a 64-bit integer that represents a file time value. The function returns a DateTime value.

The M Code Behind DateTime.FromFileTime

The DateTime.FromFileTime function is a built-in function in Power Query M. It's a part of the DateTime module in M language that provides various functions related to date and time values.

The M code for the DateTime.FromFileTime function is as follows:

(FileTime as number) as nullable datetime =>

let

timeDiff = #duration(0, 0, 0, FileTime / 10000000 – #datetime(1601, 1, 1, 0, 0, 0)),

result = #datetimezone(DateTime.LocalNow() – timeDiff, DateTimeZone.Local)

in

result

Let's break this code down into smaller parts and understand what each line of code does.

The Input Parameter

The input parameter for the DateTime.FromFileTime function is `fileTime`, which is a number data type representing the file time value. In the M code, the input parameter is named `FileTime`.

The let Expression

The `let` expression is used to define a local variable called `timeDiff`. This variable is used to calculate the difference between the file time value and the base time value of January 1, 1601.

let

timeDiff = #duration(0, 0, 0, FileTime / 10000000 – #datetime(1601, 1, 1, 0, 0, 0)),

In this line of code, we're using the `#duration` function to calculate the duration between two date and time values. The duration value is then used to calculate the actual DateTime value.

The #datetimezone Function

The `#datetimezone` function is used to calculate the final DateTime value. It takes two parameters as input, the first parameter is the date and time value, and the second parameter is the time zone offset.

result = #datetimezone(DateTime.LocalNow() – timeDiff, DateTimeZone.Local)

In this line of code, we're subtracting the `timeDiff` value from the current local date and time using the `DateTime.LocalNow()` function. The result is then passed as the first parameter to the `#datetimezone` function. The second parameter is the local time zone offset, which is automatically calculated based on the system's time zone settings.

The Nullable Data Type

The `nullable` data type is used to indicate that the function may return null if the input parameter is null. In the case of the DateTime.FromFileTime function, if the `FileTime` parameter is null, the function will return null.

The DateTime.FromFileTime function is a useful tool for converting Windows file time values to DateTime values in Power Query M. Understanding the M code behind this function can help in debugging and troubleshooting data transformation issues. The M code for this function is relatively simple, and by breaking it down into smaller parts, we can understand how it works.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)