List.Accumulate

D

Back to top

T

Back to top

#

Back to top

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

Understanding List.Accumulate

Before we dive into the M code behind List.Accumulate, it is important to understand how the function works. List.Accumulate takes three arguments:

– The first argument is a list of values that the function will process.

– The second argument is an initial state value that will be used as the starting point for the accumulation.

– The third argument is a function that will be applied to each element in the list, along with the current accumulated state value.

The function will process each element in the list, applying the custom function to the current element and the accumulated state value. The result of this operation will become the new accumulated state value, which will be used as the input for the next iteration of the function. This process is repeated until all elements in the list have been processed, at which point the final accumulated state value is returned as the result.

The M Code Behind List.Accumulate

The M code behind List.Accumulate is relatively simple, but it can be a bit daunting for those who are new to Power Query or programming in general. Here is an example of the M code for a simple List.Accumulate function:

let

Source = {1..10},

AccumulateFunction = (state, current) => state + current,

Result = List.Accumulate(Source, 0, AccumulateFunction)

in

Result

In this example, we are using List.Accumulate to find the sum of a list of numbers from 1 to 10. Here is what each line of the code does:

- The first line sets the Source variable to a list of values from 1 to 10.

- The second line defines the AccumulateFunction variable, which is a custom function that takes two arguments (state and current) and returns the result of adding them together.

- The third line applies the List.Accumulate function to the Source list, using an initial state value of 0 and the AccumulateFunction as the custom function.

When we run this code, the Result variable will be set to the final accumulated state value, which in this case is the sum of the numbers from 1 to 10 (55).

Advanced Uses of List.Accumulate

List.Accumulate can be used in a wide variety of scenarios beyond simple calculations, including data transformations, data cleaning, and more. Here are a few examples of how List.Accumulate can be used in more advanced scenarios:

Data Transformations

List.Accumulate can be used to transform data within a list. For example, you could use List.Accumulate to extract a specific value from each element in a list and accumulate those values into a new list. Here is an example of how this could be done:

let

Source = {{“John”, 25}, {“Jane”, 30}, {“Bob”, 40}},

AccumulateFunction = (state, current) => state & List.Transform({current{0}} & “, “),

Result = List.Accumulate(Source, “”, AccumulateFunction)

in

Result

In this example, we are using List.Accumulate to extract the names from a list of name/age pairs and accumulate them into a comma-separated list. Here is what each line of the code does:

- The first line sets the Source variable to a list of name/age pairs.

- The second line defines the AccumulateFunction variable, which extracts the name from each pair and adds it to the accumulated state value as a comma-separated string.

- The third line applies the List.Accumulate function to the Source list, using an initial state value of an empty string and the AccumulateFunction as the custom function.

When we run this code, the Result variable will be set to the final accumulated state value, which in this case is "John, Jane, Bob".

Data Cleaning

List.Accumulate can also be used to clean data within a list. For example, you could use List.Accumulate to remove unwanted characters from a list of strings and accumulate the cleaned strings into a new list. Here is an example of how this could be done:

let

Source = {“123-456-7890”, “555-555-5555”, “(999) 999-9999”},

AccumulateFunction = (state, current) => state & Text.Replace(current, “-“, “”) & “, “,

Result = List.Accumulate(Source, “”, AccumulateFunction)

in

Result

In this example, we are using List.Accumulate to remove dashes from a list of phone numbers and accumulate the cleaned phone numbers into a comma-separated list. Here is what each line of the code does:

- The first line sets the Source variable to a list of phone numbers.

- The second line defines the AccumulateFunction variable, which removes dashes from each phone number and adds the cleaned number to the accumulated state value as a comma-separated string.

- The third line applies the List.Accumulate function to the Source list, using an initial state value of an empty string and the AccumulateFunction as the custom function.

When we run this code, the Result variable will be set to the final accumulated state value, which in this case is "1234567890, 5555555555, 9999999999".

List.Accumulate is a powerful tool for processing lists of values in Power Query. By understanding the M code behind List.Accumulate and exploring its advanced uses, you can unlock new possibilities for data transformations and cleaning within your own Power Query workflows.

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)