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
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
Hello, 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 AddColsIterativelyThank 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 )
