Loop Through List and Add Columns

[Nie]

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 loop to do this and I have googled how to emulate loops in M. However, the articles I found were on how to calculate and accumulate some values instead of calling some M functions.

Is there a way to achieve an equivalent of a loop in M to perform a Table.AddColumn() based on the number of items in a list?

[Nie]

Thanks @Vera_33. I don't know how it works but it does.

I understand with the double [][] above that you are creating a record and then referencing a specific field value. In this case, it is [result]. And the strategy to add columns based on a dynamic list is to use a temporary record.

I tried simplifying your code to understand how it works by manually adding a step and then using the following:

= [
   counter = List.Count({"AAA"}),
   columnName = {"AAA"}{0},
   tempTable = Table.AddColumn(myTable, columnName, each columnName),
   result = tempTable
  ][result]

But when doing so I see the following when I look at the entire record.

When I look at [result] specifically, I get the error "Expression.Error: We cannot convert a value of type Function to type Table".

How and why does your code work?

[Vera_33]

Hi @Nie 

The [][] replaces the let...in...structure, it is a custom function called fxAddColumn

    fxAddColumn = (T as table, N as number) =>
         let
             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) 
         in
             result 

 then you can call this function to do recursion, I am not sure how you add the code to your current table, as this is actually a table function: Table.AddColumn

[Nie]

Thank you @Vera_33 for explaining. Turns out I had a misconception. I previously thought your code involves creating records. I didn't know let..in can be substituted using [][].

[Vera_33 · Accepted solution]

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

[petrawiggin]

Hello, @Vera_33. I am trying to apply your solution to my own situation and am not clear on two things and then have an extension question:

1. when you say 

yourStep = some steps...,​

is that the contents of the previous step in the query? For example, before I make the list values into columns, I generated the list with this line of code, so is that what would be replacing that line of your code?

#"MyPreviousStep" = List.Distinct(Table.Column(#"The step which fixed the data in the column I need to work with","ColumnIAmMakingIntoAList")),

• when I did that and otherwise pasted in your code (but with my table and column names), the function was added as a step that returned this error:

The name 'fxAddColumn' wasn't recognized.  Make sure it's spelled correctly.​

• If I am able to get this to work I want to populate the new columns with the value of another column if the value in the column I got the names of the new columns from matches the new column's name. So, in English the steps would be the following except that probably 2 & 3 would have to be combined so that they could keep using the same variables:

1) Make list of unique values in CaughtByAutomation column.
2) For each unique value in that list, create a new column using that unique value's name.
3) Populate each new column with the value of the Count column IF the value in that row in the CaughtByAutomation column matches the name of the current column.​

How might that work? The steps I use now for that when I am manually creating the new columns is like this for each new column:

= Table.AddColumn(#"Replaced empty automation with 10% Manual", "# Caught by Automation", each if [Caught by Automation] = "Automation" then [Count] else "")​

Thank you for any help you might be able to provide!

[Nie]

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]

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
    )