BIN2DEC in Power Query: Implementations of List.Accumulate. Part 1

After @datachant have posted on using List.Accumulate in Power Query, I was intrigued of practical implementations of this function.

As you can see from his post, List.Accumulate takes a list as an input parameter, and then, starting from initial seed value, invokes the same accumulator function on each element of list, consequently.

You just need to remember than List.Accumulate can use two parameters in accumulator function, state and current: state (i.e. current result of previous calculations) of accumulator  and a current element from given list. Final result will be a value that we got on the last iteration of accumulator function. Please note that seed value actually used as the first state value in accumulator.

The power of List.Accumulate is in iterations: we will execute accumulator function as many times as the number of elements in list. So, if we have an algorithm that needed repeating calculations on list members or as much times as list members count, List.Accumulate is the answer.

I found a small algorithm that shows a practical use and core concepts of List.Accumulate: conversion from binary number to decimal. In this algorithm bits of the binary number are used one by one, starting with the most significant (leftmost) bit. Beginning with the value 0, you need to repeatedly double the prior value and add the next bit to produce the next value.

So, if we would like to convert binary 11012 to decimal number 1310, we need to split binary number to the list of bits:

{1,1,0,1}

Then we take seed value of 0 and perform next operations: double the prior value and add the next bit, repeat while we have bits:

(seed, or first state) *2 + 1(current bit) = 1
(state) * 2 + 1(current bit) = 3
3(state) * 2 + 0(current bit) = 6
6(state) * 2 + 1(current bit) = 13

In this algorithm we can see all parameters for List.Accumulate: given list of bits from binary code, initial seed value of 0, state of accumulation transferred from the one step to the next step, and current element of a list (current bit).

To implement this algorithm in Power Query List.Accumulate we just need to get a list from given input, for example it is a text string: “101010”

The result could be nothing than 42, it’s quite universal 🙂

I hope that this example will help you to find more practical implementations of List.Accumulate. And if you remember that initial list could be used just as iterations counter, and a seed value could be any… to be continued.

Share this
Stacking non-nested groups of repeating columns in Power Query
SUMPRODUCT() and For Each loops in Power Query: Implementations of List.Accumulate. Part 2

Comments

Leave a Reply

Your email address will not be published / Required fields are marked *