Sep 23, 2019

2 Easy Steps to Loop with Power Query! List.Accumulate()

Updated: Jul 22, 2020

Here's the situation:

I have a list of values and want to create one single concatenated string based on that list.

We can do accomplish this with:

List.Accumulate()

For example, I have a list:

Name
Brent
Susan
Josef
Mary

To convert into this:

"BrentSusanJosefMary"

Here's the solution:

1. Convert your table to a list.

You can do this with in the ribbon via: Transform -> Convert to List

2. Insert the following code as the next Step:

= List.Accumulate(
    Name,
    "",
    (accumulatedString, curString)=>accumulatedString & curString
    )

"Name" is the variable for the list you made for #1.
"" is a placeholder for a seed. I left this as blank.
accumulatedString is our variable to keep track of the concatenated string.
curString is the variable for the current item in the list (during the loop).

The entire M code looks like this:

let
 
    // Standard stuff here...
    Source = Excel.CurrentWorkbook()
        {[Name="TableOfNames"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(
            Source,{{"Name", type text}}),

    // Change Table to a List
    Name = #"Changed Type"[Name],

    // Use a Looping function to Concatenate
    AllValuesString = List.Accumulate(
            Name,
            "",
            (accumulatedString,curString) =>
                  accumulatedString & curString)
in
    AllValuesString