List.Alternate

Updated on
Rick de Groot

List.Alternate is a Power Query M function that creates a new list with odd-numbered offset elements from the input list, depending on specified parameters. The function returns a list comprised of alternating values based on parameters such as count, repeatInterval, and offset.

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

Syntax

List.Alternate(
   list as list,
   count as number,
   optional repeatInterval as nullable number,
   optional offset as nullable number,
) as list
ArgumentAttributeDescription
ListThe list to extract values from
CountSpecifies number of values that are skipped each step.
repeatIntervalOptionalIndicate how many values are extracted in between the skipped values.
offsetOptionalSpecifies the offset after which the function begins skipping values.

Description

The selection is based on specific parameters that you can control. The count parameter determines the number of values that are skipped in each iteration. The repeatInterval parameter, which is optional, indicates how many values are extracted between the skipped values. This allows you to create a pattern of alternating values in your new list. The ‘offset‘ parameter, also optional, sets the initial offset from which values are skipped. This gives you control over where the pattern of skipping and adding values begins.

Examples

To understand the List.Alternate function better, let’s dive into some examples.

The ‘count’ parameter in List.Alternate specifies the number of values to skip at the start of the list. When providing only the first two arguments, the function retrieves all characters after the skipped characters. 

List.Alternate( { 1..10 }, 0 )       // Equals { 1..10 } skips no value
List.Alternate( { 1..10 }, 3 )       // Equals { 4..10 } skips 3 values

The List.Alternate function is designed to repeat an extraction pattern. This means you can instruct it how many values to skip and how many to extract, and this pattern will repeat itself.

The ‘count’ argument shows how many values to skip in each step. The third argument, ‘repeatInterval’, then instructs how many values to extract in each step. For instance:

// The repeated operation skips 0 values (arg 1) then returns 1 value (arg 2)
List.Alternate( { 1..10 }, 0, 1 )    // Equals { 1..10 }

// The repeated operation skips 1 value (arg 1) then returns 1 value (arg 2)
List.Alternate( { 1..10 }, 1, 1 )    // Equals { 2, 4, 6, 8, 10 }

// The repeated operation skips 1 value (arg 1) then returns 2 values (arg 2)
List.Alternate( { 1..10 }, 1, 2 )    // Equals { 2, 3, 5, 6, 8, 9 }

// The repeated operation skips 2 values (arg 1) then returns 1 value (arg 2)
List.Alternate( { 1..12 }, 2, 1 )    // Equals { 3, 6, 9, 12 }

When providing the second and third argument with larger numbers, interesting patterns emerge:

// The repeated operation skips 2 values (arg 1) then returns 4 values (arg 2)
List.Alternate( { 1..12 }, 2, 4 )    // Equals { 3, 4, 5, 6, 9, 10, 11, 12 }

// The repeated operation skips 3 values (arg 1) then returns 3 values (arg 2)
List.Alternate( { 1..12 }, 3, 3 )    // Equals { 4, 5, 6, 10, 11, 12 }

To create even more variations, we can also include List.Alternate’s fourth argument, the ‘offset’. This instructs the function to start skipping values only starting after the specified offset.

// Skips 1 value (arg 1) then returns 1 values (arg 2),  start skipping from offset 0(arg 3)
List.Alternate( { 1..10 }, 1, 1, 0 ) // Equals { 2, 4, 6, 8, 10 }

// Skips 1 value (arg 1) then returns 1 values (arg 2), start skipping from offset 1(arg 3)
List.Alternate( { 1..10 }, 1, 1, 1 ) // Equals { 1, 3, 5, 7, 9 }

// Skips 1 value (arg 1) then returns 1 values (arg 2), start skipping from offset 4(arg 3)
List.Alternate( { 1..10 }, 1, 1, 4 ) // Equals { 1, 2, 3, 4, 6, 8, 10 }

In conclusion, the List.Alternate function allows for intricate manipulation of lists based on specific parameters.

Learn more about List.Alternate in the following articles:

Other functions related to List.Alternate are:

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

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy