M Query Guide

Transformations

M Splitting Columns

Splitting Columns

M splitting columns uses Table.SplitColumn for text parsing.

Introduction to Column Splitting in M

Column splitting is a common data transformation task in Power Query M, used to split a single column into multiple columns based on a delimiter or a fixed width. The Table.SplitColumn function is a powerful tool for achieving this in M, allowing developers to manage and manipulate data efficiently.

Understanding Table.SplitColumn

The Table.SplitColumn function in M is used to divide a column into multiple columns based on specified criteria. This can include splitting by a character delimiter, by position, or even by a number of characters.

Basic Syntax of Table.SplitColumn

The basic syntax of the Table.SplitColumn function is as follows:

Example: Splitting a Column by Delimiter

Let's consider a simple example where you have a column with full names in the format "FirstName LastName" and you want to split it into two separate columns: "FirstName" and "LastName".

Explanation of the Code

In this example, the Table.FromRecords function creates a table with a single column called "Name." The Table.SplitColumn function takes this table, the column to split ("Name"), the Splitter.SplitTextByDelimiter function with a space as the delimiter, and an array of new column names ("FirstName", "LastName"). This results in a table with two new columns: "FirstName" and "LastName."

Advanced Splitting: Fixed Width

Sometimes, you may need to split a column based on fixed character widths. This is useful in scenarios where data is consistently structured in a fixed format. Here's an example of how to split a column by fixed width.

Conclusion

Splitting columns in M using the Table.SplitColumn function is a robust method for parsing and organizing data. Whether you're splitting by delimiters or fixed widths, understanding this function expands your ability to handle complex data transformations efficiently. Explore these examples and try applying them to your datasets for better data management.

Transformations

Next
Replacing Values