List.Alternate

D

Back to top

T

Back to top

#

Back to top

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

Overview of List.Alternate Function

The List.Alternate function has the following syntax:

List.Alternate(list1 as list, list2 as list) as list

The function takes two lists as inputs and returns a new list that contains the elements from both lists, alternating one from each list. The two lists must be of the same length, or an error will occur.

For example, suppose we have the following two lists:

list1 = {1, 2, 3}

list2 = {4, 5, 6}

The List.Alternate function applied to these lists will return the following result:

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

Understanding the M Code Behind List.Alternate Function

To understand how the List.Alternate function works, we need to take a closer look at the M code behind it. In Power Query, all functions are written in M, a functional programming language. The M code for the List.Alternate function is as follows:

(list1, list2) =>

let

list1count = List.Count(list1),

list2count = List.Count(list2),

combinedcount = list1count2,

zipped = List.Zip({list1, list2}),

expanded = List.Transform(zipped, each _{0}),

result = List.FirstN(List.Flatten(expanded), combinedcount)

in

result

The function takes two lists as parameters and performs the following steps:

1. Calculate the length of the first list using the List.Count function

2. Calculate the length of the second list using the List.Count function

3. Calculate the total number of elements in the new list by multiplying the length of the first list by 2

4. Combine the two lists into a single list using the List.Zip function

5. Expand the combined list by selecting the first element from each pair using the List.Transform function

6. Flatten the expanded list into a single list using the List.Flatten function

7. Select the first N elements from the flattened list, where N is equal to the combined count calculated in step 3, using the List.FirstN function

It is important to note that the List.Alternate function only works with two lists. If you need to alternate elements from more than two lists, you will need to use a different function.

Examples of List.Alternate Function

Here are some examples of how to use the List.Alternate function in Power Query:

Example 1:

Suppose we have two lists containing the following data:

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

list2 = {1, 2, 3}

We can use the List.Alternate function to alternate the elements from these two lists as follows:

List.Alternate(list1, list2)

The result will be:

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

Example 2:

Suppose we have two lists containing the following data:

list1 = {10, 20, 30}

list2 = {100, 200, 300}

We can use the List.Alternate function to calculate the average of the two lists as follows:

List.Average(List.Alternate(list1, list2))

The result will be:

165

The List.Alternate function in Power Query is a powerful tool for alternating elements from two lists. Understanding the M code behind this function can help users better understand how it works and how to use it effectively. By using the List.Alternate function, users can easily combine and transform data from multiple sources, saving time and improving efficiency.

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)