apply a transformation for every column name

[ValeriaBreve]

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

[ronrsnfld]

I'm not quite sure what your transformations is going to be doing, but, in general:

1. Create a list of columns to be transformed
2. 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")

[AlexisOlson]

@ImkeF Has a good article on this:

https://www.thebiccountant.com/2019/12/18/advanced-transformation-multiple-columns-at-once-in-power-...

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} ) )
[...]

[ValeriaBreve]

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

[AlexisOlson · Accepted solution]

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 ....

[ValeriaBreve]

THANK YOU!!! I understand now - and sorry for my replying late, my computer broke down 😞 .

Can I ask an additional question - if I wanted to limit the application of this to a number of columns, say all column numbers > 5, is it possible?

Thanks again!

Kind regards

Valeria

[AlexisOlson]

Instead of Table.ColumnNames(Source), use whatever list of column names you want to transform.

If you want to transform all except the first 5 columns, then you can use List.Skip to remove those from the list of all column names.

List.Skip(Table.ColumnNames(Source),5)

For more advanced filtering, you can use List.Select with whatever selection criteria you'd like.

[ValeriaBreve]

Thank you! It works like a charm. What do you suggest as best approach to better learn M? Thanks again!