Splitting a Date-Time Column in M (Power Query)

This document provides a function in M (Power Query) designed to split a 'date-time' column into separate 'date' and 'time' columns. It follows best practices including input validation, comprehensive documentation, and inline comments.

Function Definition

Code Snippet

let
    // Function to split a 'date-time' column into 'date' and 'time' columns
    SplitDateTime = (inputTable as table, dateTimeColumn as text) as table =>
    let
        // Check if the dateTimeColumn exists in the provided table
        ValidatedTable = if Table.HasColumns(inputTable, {dateTimeColumn}) 
                        then inputTable 
                        else error "Column not found in the input table.",

        // Split the date and time from the date-time column
        SplitColumns = Table.TransformColumns(
            ValidatedTable,
            {
                dateTimeColumn, 
                each { DateTime.Date(_), DateTime.Time(_) }, 
                type { date, time }
            }
        ),
        
        // Rename the new columns for clarity
        RenamedColumns = Table.RenameColumns(
            SplitColumns,
            {
                (dateTimeColumn, "OriginalDateTime"),
                (dateTimeColumn & "_Date", "Date"),
                (dateTimeColumn & "_Time", "Time")
            }
        )
    in
        RenamedColumns
in
    SplitDateTime

Detailed Explanation

Function Purpose

• The SplitDateTime function takes an input table and a specified column containing date-time values. It splits this column into two new columns: one for the date and another for the time.

Parameters

• inputTable: A table input that includes a date-time column.
• dateTimeColumn: A text value indicating the name of the column to be split.

Return Type

• The function returns a new table with the original date-time column replaced by separate date and time columns.

Exceptions

• The function raises an error if the specified column does not exist in the input table.

Key Steps

1. Column Existence Check: Verifies that the specified date-time column exists in the input table. If it does not, an error is raised.
2. Split Operation: The Table.TransformColumns function is used to split the date-time into date and time, creating a nested table structure.
3. Rename Columns: Newly created columns are renamed to "Date" and "Time" for user clarity while keeping the original column name as a reference for the user.

Usage Example

To demonstrate the usage of the SplitDateTime function, let’s consider the following example:

Assume we have the following table:

Code Example

let
    // Sample Input Table
    SourceTable = Table.FromRecords({
        [ID = 1, DateTime = #datetime(2023, 1, 1, 13, 45, 0)],
        [ID = 2, DateTime = #datetime(2023, 1, 2, 14, 30, 0)],
        [ID = 3, DateTime = #datetime(2023, 1, 3, 15, 15, 0)]
    }),
    
    // Call the SplitDateTime function
    ResultTable = SplitDateTime(SourceTable, "DateTime")
in
    ResultTable

Output Table

After running the code, the resulting output will be:

Conclusion

This M (Power Query) function efficiently splits a date-time column into distinct date and time columns while adhering to robust coding standards. By following this structured approach, users can ensure that their data processing tasks remain efficient and easy to understand.

For further insights and advanced techniques, consider exploring courses available on the Enterprise DNA Platform.