Table.ToList

Table.ToList is a Power Query M function that converts a table into a list by applying the specified combining function to each row of values in the table. The function returns a list containing the combined values from each row.

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

Syntax

Table.ToList(
   table as table,
   optional combiner as nullable function,
) as list

Argument	Attribute	Description
table		The table to turn into a list.
combiner	optional	Combiner Functions specify how rows should be combined. The argument can merge rows using different methods: – Combiner.CombineTextByDelimiter: using the specified delimiter. – Combiner.CombineTextByEachDelimiter: with each delimiter in a sequence. – Combiner.CombineTextByLengths: based on specified lengths. – Combiner.CombineTextByPositions: according to specified positions. – Combiner.CombineTextByRanges: using positions and lengths.

Description

Table.ToList converts the table into a list, applying a combining function to each row’s values within the table.

Examples

Let’s start with a simple example to understand the basics of the Table.ToList function.

Example Scenario

The Table.ToList function takes a table as input and for each row of values it combines them using a combiner function. Most standard library Combiner Functions require a text value as input. With that in mind, let’s look at an example.

In this example, we have a 4-column table, containing only text values. If your goal is to concatenate all row values into a list separated by commas, you can make use of the following expression:

Table.ToList( 
  Source,
  Combiner.CombineTextByDelimiter( ", " ) 
)

Both the input and output table look as follows:

In this example, the Combiner.CombineTextByDelimiter function uses “, ” as the delimiter to separate the text values in each row. You can try this by using the following code:

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

Handling Non-Text Values: Custom Combiner Function

When working with tables that contain non-text values, using the Table.ToList function will result in an error. Power Query will throw the following error if it encounters a non-text value: “Expression.Error: We cannot convert the value x to type Text”. Here, “x” represents any non-text value in your table.

If you need to concatenate rows containing non-text values, you can use a custom combiner function to convert all values to text before combining them. Here’s how:

Table.ToList( 
  Source, 
  (x) => Combiner.CombineTextByDelimiter(", ")
    ( List.Transform( x, each Text.From( _ ) ) ) 
)

How It Works:

• List Transformation: The List.Transform function converts each value in the row to text using Text.From.
• Combiner Function: The Combiner.CombineTextByDelimiter function then concatenates these text values, separating them with a comma.

To try this yourself, this is the full code:

let
  Source = 
    #table(
      type table[ProductKey = Int64.Type, Product = Text.Type, OrderDate = Text.Type, Sales = Text.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.ToList( Source, (x) => Combiner.CombineTextByDelimiter(", ")
( List.Transform( x, each Text.From(_) ) ) )
in
    TableToColumns

Related functions

Other functions related to Table.ToList are:

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

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