Date.From

Updated on

Date.From is a Power Query M function that returns a date value from the given value, with an optional culture parameter. The function returns a date value based on the input type.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Date.From(
   value as any,
   optional culture as nullable text,
) as nullable date
ArgumentAttributeDescription
valueThe value to extract a date from.
cultureoptionalThe culture argument enables the specification of a Culture code (e.g., “nl-NL” or “en-US”) to align transformations with local formatting conventions. If this argument is omitted, functions default to Culture.Current, which reflects the system’s regional settings.

Description

The Date.From function is designed to convert a variety of input types into a date value. It accepts a value of any type and an optional culture parameter, such as “en-US”. The function is capable of interpreting text, datetime, datetimezone, and number types to extract or calculate a date. If the input is already a date, it simply returns that date. However, if the input is null, the function also returns null.

Values of the following types can be converted to a date value:

  • text: Converts text to a date value. For more information, see Date.FromText.
  • datetime: Extracts the date part from a datetime value.
  • datetimezone: Retrieves the date part from the local equivalent of a datetimezone value.
  • number: Obtains the date from the datetime representation of an OLE Automation Date specified by the number.

Should the input be of an incompatible type, Date.From results in an error.

Examples

Converting a Number to a Date

In Power Query, numbers can represent dates based on the OLE Automation Date system. The Date.From function allows you to convert these numeric representations into actual date values. For example, to convert the number 45500 to a date:

// Output: #date( 2024, 7, 27 )
Date.From( 45500 )

This method is particularly useful in scenarios where dates are stored as serial numbers, a common practice in various data systems.

Extracting a Date from DateTime

Often, data comes in the form of a datetime value, but you might only need the date component. The Date.From function can extract just the date part from a datetime value. For instance:

// Output: #date( 2024, 12, 31)
Date.From( #datetime( 2024, 12, 31, 07, 15, 09 ) )

This usage is beneficial when you need to simplify datetime values into dates for reporting or further data analysis in tools like Power BI or Excel.

Extract a Date from Text

Similarly, the Date.From function can turn a text value into a date. For instance:

Date.From( "25 Dec 2024" ) // Output: #date( 2024, 12, 25 )
Date.From( "31 January 1900" ) // Output: #date( 1900, 1, 31 )

For culture specific extractions, you can fill in the optional culture code.

Date.From( "31 mrt 2024", "nl-NL" ) // Output: #date( 2024, 3, 31 )

Other functions related to Date.From are:

BI Gorilla Youtube Channel

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/date-from