Table.SplitAt

What is the Table.SplitAt Function?

The Table.SplitAt function in Power Query is used to split a table into two parts based on a given delimiter. The delimiter can be a column index or a column name. The function returns two tables – the first table contains the columns before the delimiter, and the second table contains the columns after the delimiter.

Understanding the M Code Behind the Table.SplitAt Function

The M code behind the Table.SplitAt function is quite simple. Let’s take a look at the code:

In the code above, ‘Source’ refers to the table that needs to be split, and ‘Index’ refers to the delimiter column index or name. The Table.SplitAt function takes in these two parameters and returns two tables – the first table contains the columns before the delimiter, and the second table contains the columns after the delimiter.

Using the Table.SplitAt Function

Using the Table.SplitAt function is easy. Let’s take an example to understand how it works.

Suppose we have a table ‘SalesData’ that contains the following columns – ‘Product’, ‘Category’, ‘Sales’, ‘Profit’, and ‘Date’. We want to split this table into two parts based on the ‘Profit’ column.

To do this, we can use the following M code:

In the code above, we first define the ‘Source’ variable as the ‘SalesData’ table. We then use the Table.ColumnIndex function to get the index of the ‘Profit’ column. Finally, we use the Table.SplitAt function to split the table into two parts based on the ‘Profit’ column.

The Table.SplitAt function in Power Query is a powerful tool that can be used to split a table into two parts based on a given delimiter. The M code behind the function is quite simple and easy to understand. By using this function, data analysts and business intelligence professionals can easily split large tables into smaller, more manageable tables, making data analysis and preparation much easier.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us