Forum Discussion
apply a transformation for every column name
Hello,
I would like to apply a transformation for every column of my table.
I found in the community how to get the column names
Table1_Table = #"Imported Excel Workbook"{[Item="Table1",Kind="Table"]}[Data],
step1 = Table.ColumnNames(Table1_Table),
#"Converted to Table" = Table.FromList(step1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
and the transformation I would like to apply is:
Table.TransformColumns(PreviousStepName,{"ColumnName",each if _=null then _ else "ColumnName"})
How do I write the "for each" code that allows me to apply that transofrmation to each column name?
Thanks!!!
Kind regards
Valeria
Here's a simplified example you can paste into the Advanced Editor of a new blank query:
let Source = #table({"Column1", "Column2", "Column3"}, {{"a",null,"d"},{null,"b","e"},{"c",null,null}}), #"Replace Nulls" = Table.TransformColumns(Source, List.Transform(Table.ColumnNames(Source), (col) => {col, each _ ?? col, type text})) in #"Replace Nulls"What this does is take the list of column names {"Column1", "Column2", "Column3"} and turns each one into a transformation rule. For example, "Column1" --> {"Column1", each _ ?? "Column1", type text} so the second step expands to
Table.TransformColumns(Source, {{"Column1", each _ ?? "Column1", type text}, {"Column2", each _ ?? "Column2", type text}, {"Column3", each _ ?? "Column3", type text}})Note: ?? is the coalesce operator, a more compact way of handling if _ = null then ....
7 Replies
ImkeF Has a good article on this:
The key part is getting the list of column names and expanding that into a list of transformations:
[...] columnNames = if ColumnNames = null then Table.ColumnNames(Table) else ColumnNames, Transformation = Table.TransformColumns( Table, List.Transform(columnNames, each {_, Function, TypeForColumns} ) ) [...]Thanks, sorry I need a bit more hand-holding to get through this.
So in the end I need to loop through all my columns in the table, whatever their name is, and if the value in any of the cells for the given column is <> null then I need to replace it with the column name itself.
I was able to get the list of the column names, that part is OK, but after that I don't understand how I need to proceed...
Thanks for your patience and help!
Kind regards
Valeria
Here's a simplified example you can paste into the Advanced Editor of a new blank query:
let Source = #table({"Column1", "Column2", "Column3"}, {{"a",null,"d"},{null,"b","e"},{"c",null,null}}), #"Replace Nulls" = Table.TransformColumns(Source, List.Transform(Table.ColumnNames(Source), (col) => {col, each _ ?? col, type text})) in #"Replace Nulls"What this does is take the list of column names {"Column1", "Column2", "Column3"} and turns each one into a transformation rule. For example, "Column1" --> {"Column1", each _ ?? "Column1", type text} so the second step expands to
Table.TransformColumns(Source, {{"Column1", each _ ?? "Column1", type text}, {"Column2", each _ ?? "Column2", type text}, {"Column3", each _ ?? "Column3", type text}})Note: ?? is the coalesce operator, a more compact way of handling if _ = null then ....
I'm not quite sure what your transformations is going to be doing, but, in general:
- Create a list of columns to be transformed
- Using List.Transform, transform that list to a list of transformations in the proper format for the Table.TransformColumns argument
Example:
#"Columns to Transform" = Table.ColumnNames(#"Previous Step"), #"Transformation List" = List.Transform(#"Columns to Transform",(li)=>{li, each if _ = null then _ else li}), Transform = Table.TransformColumns(#"Previous Step", #"Transformation List")
