Table.ToColumns

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Table.ToColumns

Understanding the M Code

Before we dive into examples of Table.ToColumns, let’s take a moment to understand the M code behind this function. When you use Table.ToColumns, Power Query generates M code that takes the table you want to convert and transforms it into a list of columns. The M code generated looks something like this:

let

Source =

,

Columns = Table.ColumnNames(Source),

Output = List.Transform(Columns, each Table.Column(Source, _))

in

Output

Here’s a brief overview of what each part of this code does:

– The `Source` variable contains the table you want to convert.

– The `Columns` variable contains a list of column names from the table.

– The `Output` variable uses the `List.Transform` function to loop through each column in the `Columns` list and extract the values from that column in the `Source` table.

The end result is a list of columns that can be used for further data manipulation.

Example Usage

Now that we have a basic understanding of the M code behind Table.ToColumns, let’s explore some practical examples of how it can be used.

Example 1: Splitting a Column into Multiple Columns

One common use case for Table.ToColumns is to split a column in a table into multiple columns. For example, let’s say you have a table with a column called “Full Name” that contains both the first and last name of each person. You can use Table.ToColumns to split this column into two separate columns containing just the first and last names.

Here’s the M code to do this:

let

Source =

,

SplitName = Table.SplitColumn(Source, "Full Name", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"First Name", "Last Name"}),

Output = Table.ToColumns(SplitName)

in

Output

Example 2: Creating a Pivot Table

Another useful application of Table.ToColumns is to create a pivot table from an existing table. For example, let’s say you have a table that contains sales data for a department store. You can use Table.ToColumns to pivot this data and create a summary table that shows total sales for each product category.

Here’s the M code to do this:

let

Source =

,

Pivot = Table.Pivot(Source, List.Distinct(Source[Product Category]), "Product Category", "Sales", List.Sum),

Output = Table.ToColumns(Pivot)

in

Output

Example 3: Unpivoting Data

Finally, Table.ToColumns can also be used to unpivot data in a table. For example, let’s say you have a table that contains sales data for each quarter of the year, with separate columns for each quarter. You can use Table.ToColumns to unpivot this data and create a new table with separate rows for each quarter and sales amount.

Here’s the M code to do this:

let

Source =

,

Unpivot = Table.UnpivotOtherColumns(Source, {"Product", "Category"}, "Quarter", "Sales"),

Output = Table.ToColumns(Unpivot)

in

Output

Table.ToColumns is a powerful function in Power Query that can be used to transform tables into lists of columns. By understanding the M code behind this function and exploring practical examples of how it can be used, you can take your data analysis and transformation skills to the next level.

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

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)

    © 2024 G Com Solutions Limited