Forum Discussion
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
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.
Super User1. 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