The Antares Alien

Game Dev Blog

  • Brent Jones

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


Here's the situation:

I have a list of values (text/string). I want to create one single concatenated string based on that list.

For example, I have a this list:

Name

Brent

Susan

Josef

Mary

I needed a concatenated string like 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 "= List.Accumulate(Name,"",(accumulatedString,curString)=> accumulatedString & curString)" as the next Step.

  • "Name" is the variable for the list you made for #1.

  • "" is a placeholder for a seed. I left this as blank.

  • The final parameter for List.Accumulate is a function which takes an input and an output.

  • 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