I must admit that I had more than one unsuccessful attempt to try to fully understand how the List.Alternate-function works. What helped me at the end, was the function Table.AlternateRows. It pretends to be similar to List.Alternate, but holds some surprises that I will uncover in this blogpost:
How Table.Alternate works
Say I have the table below and want to retrieve just the letters that appear in every 2nd row:
Table.Alternate – Remove every other row
I find the dialogue that appears very helpful and intuitive:
It clearly is a removal operation and here I want to remove the 1st row from my table (“1”), and just one at a time. Also want to keep just one row (“A”) before the next one is removed (“2”) and so on.
In the formula bar, this step will be translated into this M-code:
If you would have expected it to be translated to: Table.AlternateRows(Source,1,1,1) instead, you might have forgotten that the M-language in Power Query starts to count at 0, so the first row to remove is expressed by the 0 here.
List.Alternate should work similar
So if my input is a list instead of a table like below, I should expect a similar result than the sample above if I tweak the code a bit, shouldn’t I?
List.Alternate – produces a different result
But hey: What’s wrong here? Not a single element has been removed from the list !!
So let’s have a look into the documentation:
List.Alternate – Function Documentation
and compare it with the Table.AlternateRows documentation:
Table.AlternateRows – Function Documentation
Hm – at least we have one match here: The “offset” parameter is included in both functions. But it is the first (number) parameter in the Table-function and is at the last position in the List-function. So let’s move it around then like this:
List.Alternate – same result with different parameter order
There we are 🙂
So the order of the function parameters is different here. Also the other parameter names are different and their description. I find them much easier to understand in the Table function and of course, the function dialogue there helps to understand what shall happen as well.
Enjoy & stay queryious 😉