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