Forum Discussion

Nie's avatar
Nie
Helper I
5 years ago
Solved

Loop Through List and Add Columns

I have a list that changes dynamically. It can have 10 items now, but 100 items later. I want to be able to add a corresponding column to my table for each item in this list.   I think I need a loo...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Nie 

     

    So you have a table, and a list. You need to add a column for each item in the list, and the column name / values are all that item from the list? If yes, one way to do it

     

     

    let
    yourStep = some steps...,
    fxAddColumn = (T as table, N as number) =>
         [
             counter = List.Count(yourList),
             columnName = yourList{N},
             tempTable = Table.AddColumn(T, columnName, each columnName),
             result = if N>= counter-1 then tempTable else @fxAddColumn(tempTable,N+1) 
         ][result], 

    Custom = fxAddColumn(yourStep,0)
in
    Custom

     

     

Nie's avatar
Nie
Helper I
2 years ago

Thanks to the help in this forum and excelisfun, I've gotten fairly advanced at M code so I want to contribute back.

 

Recursive functions can be very difficult to wrap your head around. Here's another method to iteratively add columns to a table based on a list.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJRitWJVjICskzBLGMgy0wpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]),

    MyList = {"C", "D", "E", "F", "G"},

    AddColsIteratively = List.Accumulate(
        MyList,  // Items to iterate over.
        Source,  // The table (or anything) that I want to modify iteratively and the changes retained. This is the first "prev".
        (prev as table, current as text) =>
            let
                AddColumn = Table.AddColumn(prev, current, each "Hello")
            in
                AddColumn
    )
in
    AddColsIteratively

 

petrawiggin's avatar
petrawiggin
Helper I
2 years ago

Thank you, Nie! This is just what I needed. 
My final version adds some text to the beginning of the dynamic column names and fills the values of those columns based on whether the value of they column it got its name from matches the current column's name and then if so, pulls a value from another column. Fun times!

SourceForCountColumns = MainTable,

#"Make list of review type values" = List.Distinct(MainTable[Type of Review]),

    #"Add count columns for all review type values" = List.Accumulate(
        #"Make list of review type values",  // Items to iterate over.
        SourceForCountColumns,  // The table (or anything) that I want to modify iteratively and the changes retained. This is the first "prev".
        (prev as table, current as text) =>
            let
                AddColumn = Table.AddColumn(prev, ("# " & current), each if [Type of Review] = current then [Count] else "", Int64.Type)    //make a column where the name starts with # then is the value of the next unique value of the Type of Review column and the value is the value of the Count column for every row where the value of the Type of Review column is the one we're currently on
            in
                AddColumn
    )