DateTime.Type

Updated on

DateTime.Type is a type facet in the Power Query M language. It represents the default type claim for the ‘type datetime’. It is used primarily for interactions with external systems where detailed timestamp information is crucial. The base type of ‘type datetime’ in Power Query provides precise time details (including both date and time elements).

Examples

The best way to understand the usage and functionality of DateTime.Type is through practical examples. Let’s take a few scenarios where DateTime.Type can be crucial.

When adding a new column in Power Query, specifying the data type of the new column is an essential practice. This not only keeps your data consistent but also prevents potential errors or issues that might arise due to data type mismatches.

Consider the following example where we add a new column named “Value” to a table “Source”. You can ascribe a data type to your value by specifying the 4th optional argument of Table.AddColumn.

Table.AddColumn(
   Source, 
   "Value", 
   each DateTime.FromText( "2023-06-07T12:30:45" ), 
   DateTime.Type 
)

DateTime.Type also proves to be invaluable when creating tables from scratch in Power Query. The #table function allows you to specify a data type before providing the desired column values. Consider the following example where we define a column in a table to be of DateTime.Type:

#table( 
   type table [ Value = DateTime.Type ], 
   { { DateTime.FromText("2023-06-07T12:30:45") } } 
   )

Here, we use the #table function to create a new table. The type table statement defines the structure of the table, stating that it will have a column named “Value” of type DateTime. We then provide the DateTime value to populate this column, again using the DateTime.FromText function to convert a string to a DateTime value.

Related facets

Other related facets are:

Contribute » | Contributors: Rick de Groot