TransformColumns - Valid Functions and Multiple Transformations

[PaigeY]

"Syntax: Table.TransformColumns(table as table, transformOperations as list, optional defaultTransformation as nullable function, optional missingField as nullable number) as table"

"About: Transforms table by applying each column operation listed in transformOperations (where the format is { column name, transformation }..." - from Table.TransformColumns Article

1. What makes a function valid for use as a 'transformation'?
{"Value", Text.Trim, type text} works despite Text.Trim not listing ites first variable as optional. However the documentation for TransformColumns doesn't actually state anything about what can be used as a 'transformation'. I'm wondering if these can be complex or conditional or are you limited to a certain list of functions?

2. Is there any way to apply multiple transformations to the same column within the same step?
Ex: Is there anyway to combine {"Handle", Text.Trim, type text} and {"Handle", Text.Proper, type text} within the same Table.TransformColumns step?

[PaigeY]

1. Yes, I understood that part, but what I can't find anywhere is a definition of what makes a function a 'transform function'? Is it as simple as any function that can be applied to each cell in a column? Can you use if then to apply different functions depending on other conditions? Could I for instance do Date.From unless the field contained 1/1/1900 in which case it would replace the value with null?

2. I had tried nesting them at some point without any luck, but I can't recall if I remembered to try using each with _ as the reference. I'll give it a try and see what happens.

[AlexisOlson · Accepted solution]

1. It should be as simple as any function that works on the input should be a valid function. You can certainly write functions with conditions in them. For example, instead of Date.From, you could write

each if _ = #date(1900,1,1) then null else Date.From(_)

or equivalently

(_) => if _ = #date(1900,1,1) then null else Date.From(_)

This expression defines a function on the variable "_".

2. To nest functions, you will need to use the functional notation instead of just function names. Using the function name Text.Proper gives the same result as writing an anonymous function using underscore as the variable and applying that function to the variable:

(_) => Text.Proper(_)

This is equivalent to the following syntax shortcut (like I used above):

each Text.Proper(_)

The "each" syntax @wdx223_Daniel suggested works fine but you could write it using the "=>" notation too.

(txt) => Text.Proper(Text.Trim(txt))

This time, I chose "txt" as the variable instead of "_" for this unnamed function definition.

More info:

https://radacad.com/writing-custom-functions-in-power-query-m

https://bengribaudo.com/blog/2017/11/28/4199/power-query-m-primer-part2-functions-defining

[wdx223_Daniel]

1, the 2nd parameter, can be a list like {{"column1",each transform function}, {"column2",each transform function},....}, the column names must be unique; and the 3rd parameter is a function that transforms all other columns not listed in 2nd parameter.

2,{"Handle",each Text.Poper(Text.Trime(_))}