List.Combine in Power Query: 10 easy examples

In this post, we look at the List.Combine Power Query M function. It is a transformation function that merges a list of lists into a single list.

Along with detailed notes, there are 10 examples to show how to use the function in different contexts.

Table of Contents:

Purpose
Return Value
Syntax
Parameters
Usage Notes
Examples
Related Tutorials
Related Functions

Purpose

Takes a list of lists and merges them into one new list. Duplicates are not removed.

Return Value

A list containing all items from multiple lists (including duplicates).

Syntax

List.Combine(lists as list) as list

Parameters

lists – One or more Power Query lists. The lists can be:
- individual items contained within curly brackets e.g. {true,”Alpha”,null}.
- Any existing query or step which returns a list output.
- A function that returns a list.

Usage Notes

The List.Combine function is in the List function category; it returns a single list from multiple lists supplied inside a single parameter.

The function has a single parameter, but requires multiple lists. Therefore, the lists are wrapped within curly brackets to create a list of lists. Please pay close attention to the curly brackets in each example, as they can change the output.

List.Combine is not available from any buttons in the user interface.

List.Combine includes all items from the lists supplied. To remove duplicates the options are:

List.Union
Nest List.Combine inside List.Distinct.

Examples

Example #1: Combine two lists

Each list in the lists parameter can be standard M code lists, which are comma-separated items listed within curly brackets.

In the function below, two lists are combined into a single list.

= List.Combine({
  {"Alpha", "Bravo", "Charlie", "Delta"},
  {"Alpha", "Bravo"}
})

Returns: {“Alpha”, “Bravo”, “Charlie”, “Delta”, “Alpha”, “Bravo”}

Example #2: Combine list queries with other lists

A list included in the lists parameter can be the result of a step or another query, where that step or query’s output is a list type.

The following is a list query called Example List.

= {"Alpha", "Bravo", "Charlie", "Delta"}

The list query above can be used as a list in the lists parameter. This is demonstrated by the example below.

= List.Combine({#"Example List",{"Alpha", "Bravo"}})

Returns: {“Alpha”, “Bravo”, “Charlie”, “Delta”, “Alpha”, “Bravo”}

Example #3: Combine table columns with other lists

Within Power Query, a table column is treated as a list. Therefore, within List.Combine, a column reference can be used as a list inside the lists parameter.

The table above is named Example Table.

The code below combines three lists:

Item column from the Example Table.
Example List query (from Example 2)
M code list {“Alpha”, “Bravo”}

= List.Combine({#"Example Table"[Item],#"Example List",{"Alpha", "Bravo"}})

Returns: {“Alpha”, “Bravo”, “Charlie”, “Delta”, “Alpha”, “Bravo”, “Charlie”, “Delta”, “Alpha”, “Bravo”}

Example #4: Combine lists of lists

List.Combine operates on a list of lists. However, where those lists themselves contain sub-lists, the function does not automatically combine at the sub-list level.

= List.Combine({
  {{"Alpha", "Bravo", "Charlie"}},
  {{"Alpha", "Bravo"}}
})

In this example, double curly brackets around each list creates a list of lists of lists. To understand the impact, compare the syntax above to Example 1.

The first list contains {“Alpha”, “Bravo”, “Charlie”} and the second list contains {“Alpha”, “Bravo”}. Due to the additional curly brackets, the function only sees them as a list items.

Returns: {List, List}

Example #5: Combine lists of different data types

List.Combine can be used with any data type.

The example below combines text, dates, and a list into a single list.

= List.Combine({
  {"Alpha", "Bravo", "Charlie"}, 
  {#date(2022,9,1), {"Bravo", "Charlie"}
  }
})

Returns: {“Alpha”, “Bravo”, “Charlie”, 1 Sep 2022, List}

The last item, List, contains a sub-list with two items {“Bravo”, “Charlie”}

Example #6: Count items in multiple lists

List.NonNullCount counts the non-null items in a list. Since List.Combine merges multiple lists, this function is a useful way to count the number of items from multiple lists.

In the examples below, List.Combine contains two lists {“Alpha”, “Bravo”, “Charlie”} and {null, List}. The second item from the second list is also a list. It contains two items {null, null}.

= List.NonNullCount(
  List.Combine({
    {"Alpha", "Bravo", "Charlie"}, 
    {null, 
      {null, null}
    }
  })
)

There are only 3 non-null values contained in all the lists. However, since the {null, null} is a sub-list, the function will count that as a list item. Therefore, the value returned is 4.

Returns: {“Alpha”, “Bravo”, “Charlie”, List}

Example #7: List.Count vs List.Union

Within Power Query, there are two common functions for joining lists; List.Combine and List.Union. They work slightly differently, so let’s consider both below.

List.Union takes a list of lists and returns an output list where duplicate values are removed. This is different from List.Combine, which does not remove any duplicates.

The formula below uses List.Combine:

= List.Combine({
  {"Alpha", "Bravo", "Charlie"},
  {"Alpha", "Delta"}
})

Duplicate values are retained in the output.

Returns: {“Alpha”, “Bravo”, “Charlie”, “Alpha”, “Delta”}

The formula below uses List.Union:

= List.Union({
  {"Alpha", "Bravo", "Charlie"},
  {"Alpha", "Delta"}
})

Duplicate values are removed, leaving only the first instance of each distinct item.

Returns: {“Alpha”, “Bravo”, “Charlie”, “Delta”}

To obtain the same result as List.Union, we could nest List.Combine inside List.Distinct.

List.Distinct returns all distinct items from a single list parameter. Since List.Combine returns a single list; it can be the parameter inside List.Distinct.

= List.Distinct(
  List.Combine({
    {"Alpha", "Bravo", "Charlie"},
    {"Alpha", "Delta"}
  })
)

Returns: {“Alpha”, “Bravo”, “Charlie”, “Delta”}

The value returned by the List.Distinct and List.Combine combination is the same as List.Union.

Example #8: Combine empty lists

If supplied with empty lists, List.Combine does not return an error, but returns an empty list.

The function below includes a single empty list provided as the parameter.

= List.Combine({{}})

Returns: {}

The function below includes multiple empty lists provided as the lists parameter.

= List.Combine({{},{}})

Returns: {}

Example #9: Check if lists contain duplicate items

When working with multiple lists, we may want to test if they have duplicate items. For this, we use List.IsDistinct, it returns a logical value indicating if a list contains only distinct items (True = Distinct, no duplicates; False = Not distinct, contains duplicates)

The function below returns false as the word “Alpha” appears twice in the combined list.

= List.IsDistinct(
  List.Combine({
    {"Alpha", "Bravo", "Charlie"},
    {"Alpha", "Delta"}
  })
)

Returns: FALSE

Example #10: List.Combine vs Table.Combine

This example illustrates the difference between List.Combine and Table.Combine. To demonstrate this, two additional functions are used, Table.Combine and Table.FromList.

Table.Combine returns a table from a list of Tables.
Table.FromList converts a List into a Table

In this example, we use Example List from Example 2 and Example Table from Example 3.

The function below combines a list query with a table column into a single list.

= List.Combine({#"Example Table"[Item], #"Example List"})

Returns: {“Alpha”, “Bravo”, “Charlie”, “Delta”, “Alpha”, “Bravo”, “Charlie”, “Delta”}

In comparison, the function below combines a table with a list converted to a table.

Additional parameters are added into the Table.FromList function to declare the column name and data type.

= Table.Combine({
  #"Example Table", 
  Table.FromList(#"Example List", null, type table[Item=Text.Type])
})

Returns: A table where the list query is combined into the Item column of the table.

This example is to illustrate the different behaviors by lists and tables.