The Antares Alien

Power BI & Game Dev Blog

  • Brent Jones

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

Updated: Jul 22


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:

  1. Name

  2. Brent

  3. Susan

  4. Josef

  5. 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

Enjoy the code my fellow space travelers.

Check out some more useful(?) power query functions here.

#PowerQuery #Excel #PowerBI #M #List #Loop #Functions

Support my Indie game!
Steam

Itch.Io

Cover Art - Main_Medium_With_Title_Cropp
Cover Art - Main_Large_With_Title.png