Forum Discussion
Loop Through List and Add Columns
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
Thanks Anonymous. 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?
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 resultthen 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
Thank you Anonymous 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 [][].