Forum Discussion
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 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?
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
7 Replies
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 CustomHello, Anonymous. I am trying to apply your solution to my own situation and am not clear on two things and then have an extension question:
- 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!
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
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 [][].
