Table.ToColumns

Table.ToColumns is a Power Query M function that creates a list of nested lists from a table, where each list item is an inner list that contains the column values. The function returns a list of lists representing the columns and their respective values.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Table.ToColumns( table as table ) as list

Description

Table.ToColumns creates a list of nested lists of column values from a table. Each list item is an inner list containing the column values from table.

Examples

Let’s look at an example of what the function does. Suppose you have a table consisting of four columns. For some operations, you may need the table in a different shape.

The Table.ToColumns function stores the values of each column in a separate list. It then returns a list containing all the different lists with column values. Suppose you have a table called Source, which you want to transform. You can do that as follows:

Table.ToColumns( Source )

Applying it to a table, you can visualize it as:

You can try this yourself by pasting the following code into the advanced editor:

let
  Source = 
    #table(
      type table[ProductKey = Int64.Type, Product = Text.Type, OrderDate = Date.Type, Sales = Int64.Type ],
      {
          {1, "Product A", #date(2024, 1, 15), 150 },
          {2, "Product B", #date(2024, 2, 20), 200 },
          {3, "Product C", #date(2024, 3, 10), 175 },
          {4, "Product D", #date(2024, 4, 25), 220 },
          {5, "Product E", #date(2024, 5, 5 ), 185 },
          {6, "Product F", #date(2024, 6, 15), 240 }
      }
    ),
  TableToColumns = Table.ToColumns( Source )
in
  TableToColumns

Related articles

Learn more about Table.ToColumns in the following articles:

• Get Value from Previous Row using Power Query
  This article shows how to get the previous row value using Power Query. You can adjust the 3 ways to also retrieve an earlier or later row. » Read more
• Replace Values in Power Query M (Ultimate Guide)
  Learn how to replace values in Power Query. Look into conditional replacements, replacing in multiple columns, case sensitivity and more! » Read more

Related functions

Other functions related to Table.ToColumns are:

• Table.Partition
• Table.PartitionValues
• Table.Split
• Table.SplitAt
• Table.ToList
• Table.ToRecords
• Table.ToRows
• Table.Transpose

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-tocolumns