TransformColumns - Valid Functions and Multiple Transformations

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