Date.Type

Updated on

Date.Type is a type facet in the Power Query M language. It represents the default type claim for the ‘type date’. It is used primarily for interacting with external systems where accurate date representation is necessary. The base type of ‘type date’ in Power Query is for date values,

Examples

To truly grasp the utility and workings of the Date.Type function, we delve into some practical examples. Here are a few instances where Date.Type is used.

Adding a new column with a specified data type is a common scenario in Power Query. Specifying the data type ensures data consistency and minimizes the risk of potential errors arising from data type discrepancies.

In the following example, we add a new column named “Value” to a table, referred to as “Source”. The type of data added is specified using the 4th optional argument of Table.AddColumn, i.e., Date.Type.

Table.AddColumn(
   Source, 
   "Value", 
   each Date.FromText( "2023-06-07" ), 
   Date.Type 
)

In the above scenario, we use the Table.AddColumn function to add a new column to the Source table. The Date.FromText function is used to convert the string “2023-06-07” into a Date value, and the fourth argument, Date.Type, ensures that Power Query treats the values in this column as Date values.

Another important scenario where Date.Type is vital is when creating tables from scratch. The #table function allows you to define a column’s data type before providing the actual values. In the example below, a column in a table is defined to be of Date.Type:

#table( 
   type table [ Value = Date.Type ], 
   { { Date.FromText("2023-06-07") } } 
)

In this instance, the #table function is employed to create a new table. The type table statement outlines the structure of the table, declaring it will have a column named “Value” of type Date. Subsequently, a Date value is provided to populate this column using the Date.FromText function to convert a string to a Date value.

Related facets

Other related facets are:

Contribute » | Contributors: Rick de Groot