List.Combine

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function List.Combine

Introduction to the List.Combine Function

The List.Combine function is a part of the Power Query M language, which is used for data transformation and analysis. This function allows you to combine multiple lists into one, which can be useful when working with large datasets.

One of the key advantages of the List.Combine function is that it can handle lists of different lengths and types. This means that you can easily combine lists with different structures and formats, without having to worry about compatibility issues.

Understanding the M Code Behind List.Combine

To understand the M code behind the List.Combine function, let’s take a look at a simple example. Suppose we have two lists, List1 and List2, which contain the following data:

List1 = {1, 2, 3}

List2 = {“A”, “B”, “C”}

To combine these lists into a single list, we can use the List.Combine function as follows:

List.Combine({List1, List2})

The output of this function would be a new list with the following data:

{1, 2, 3, “A”, “B”, “C”}

The M code behind the List.Combine function is relatively simple. It takes a list of lists as its input and returns a single list that contains all the elements of the input lists.

Advanced Techniques for Using List.Combine

While the basic usage of the List.Combine function is straightforward, there are many advanced techniques that you can use to make the most of this powerful tool. Here are a few examples:

Combining Lists with Different Types

As mentioned earlier, the List.Combine function can handle lists with different types and structures. For example, you can combine a list of numbers with a list of text strings, as we saw in the previous example. However, you can also combine lists with more complex structures, such as lists of records or tables.

To combine lists with different types, you can use the List.Transform function to convert each list to a common type before combining them. For example, suppose we have the following two lists:

List1 = {1, 2, 3}

List2 = {“A”, “B”, “C”}

To combine these lists into a single list of records, we can use the following code:

List.Combine(

{

List.Transform(List1, each [Num = _]),

List.Transform(List2, each [Text = _])

}

)

The output of this function would be a new list with the following records:

{

[Num = 1],

[Num = 2],

[Num = 3],

[Text = “A”],

[Text = “B”],

[Text = “C”]

}

Combining Nested Lists

Another advanced technique for using the List.Combine function is to combine nested lists. For example, suppose we have the following two lists:

List1 = {{1, 2, 3}, {4, 5, 6}}

List2 = {{“A”, “B”}, {“C”, “D”}}

To combine these lists into a single nested list, we can use the following code:

List.Combine(

List.Transform(

List.Zip({List1, List2}),

each List.Combine(_)

)

)

The output of this function would be a new list with the following structure:

{

{1, 2, 3, “A”, “B”},

{4, 5, 6, “C”, “D”}

}

Combining Lists with Dynamic Content

Finally, you can also use the List.Combine function to combine lists with dynamic content, such as lists that are generated by other functions or queries. For example, suppose we have a list of tables that are generated by various queries:

TableList = {Query1, Query2, Query3}

To combine the data from these tables into a single list, we can use the following code:

List.Combine(

List.Transform(TableList, each Table.ToList(_))

)

This code converts each table in the TableList to a list using the Table.ToList function, and then combines those lists into a single list using the List.Combine function.

In conclusion, the List.Combine function is a powerful tool that can save you a lot of time and effort when working with large datasets. By understanding the M code behind this function and using advanced techniques such as combining lists with different types and nested lists, you can unlock its full potential and take your data analysis 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)