Year ago I wrote a post Transform table column using own function in Power Query. According to stats, it is quite popular topic, and I’m not surprised.
However, I think that old post is quite complex and too long. Advice should be shorter.
In addition, that post do not cover another very important scenario, when you need to transform column using value from another column.
Consider following case
Task:
- We need to Trim column [Name]
- We need to divide [Amount] by [Counter] (and do not want to add additional column)
Let’s do this.
1. Power Trim – clean all redundant characters
To remove unnecessary chars from string in Power Query
Use any standard text transformation
Then replace it with custom function – PowerTrim
Shortly – change
To
Details:
1.1. Standard Trim
Trim operation is a standard text transformation
However, this standard function trims only spaces from beginning and end of string. Inside the string we still have duplicated spaces.
Desired trim can be achieved with custom function. Such function was introduced by Ken Puls and called PowerTrim.
This function is also available in repository of helpful Power Query functions on Github – here.
1.2. Custom Trim
To apply custom function, firstly we need to add it to our query.
In Query Editor go to View -> Advanced Editor
Add some space and copy/paste function code, give it a name
Don’t forget to add comma after “result”.
Now we can use it in auto-generated command that appeared after transformation from UI.
In formula bar on in advanced editor – change this part
To
We must use keyword “each” and symbol “_” here.
Shortly: column is a list, we want to change “each element of list, “_” is a reference to element of list (this is how it can be used in argument of function). Details on pages 62 (§6.4.2) and 92 (§9.7) of M Language Reference (see PDF there).
You may see on pic that PowerTrim removed redundant spaces. It performs action in Power Query like TRIM in Excel.
2. Transform a column using value from another column
In sample table we need to divide [Amount] by [Counter]. However, do not want to add additional column.
I found solution of similar problem here and here.
Generalized those solutions in two methods
1 – Table.FromRecords and Table.TransformRows
2 – Table.FromRecords and Table.TransformRows and Record.TransformFields
Method 1
Combination: Table.FromRecords and Table.TransformRows
In my case I get following formula
This method requires to list ALL needed fields (columns). In some cases (like in mine) it helps to avoid next step – Remove column Counter. I “remove” it in Table.TransformRows, simply not mentioning in list of fields of resulting record.
Method 2
What if we have a table with long list of columns and we are too lazy to type all of them in Method 1?
In other words, imagine table that has 50 columns, but we need to change only one, what would be the optimal way?
We should use additional function – Record.TransformFields
How it works?
Think from: table row is just a record (set of fields and its values).
Table.TransformRows definition: Table.TransformRows(table as table, transform as function) as list
So, it iterates through table rows and applies “transform function” from second argument to each row.
Therefore, we can pass “row”-iterator (as object of type Record) to another function – Record.TransformFields.
Definition: Record.TransformFields(record as record, transformOperations as list, optional missingField as nullable number) as record
Where “transformOperations as list” should look like list of pairs
{ {FieldName1, transform as function}, {FieldName2, transform as function} … }
e.g. like in documentation { {“OrderID”, Number.FromText}, {“Price”, Number.FromText} }
In my case I need to change value in field “Amount” by dividing on value from field [Counter] of the same “row”.
To be able to reference to fields of particular “row”-iterator we must pass it to Record.TransformFields.
Name of parameter doesn’t matter, but should exist. Same function with another name of argument:
Hope, this post explains topic better :), although it is less than 100 words shorter.
Sample workbook can be found here.
Happy querying! 🙂
Hey! Cool post. Found it on twitter and the content has some great insights about how you can apply functions at a record level.
From my past experience, I wouldn’t recommend going this route unless you mix it with list and use it for the lazy evaluation. It has certain scenarios, but those scenarios are quite rare but they do exist.
Anyway, for the specific case that you talk about in this post, there’s a 3rd Method and probably the easiest one. Using the UI, do a replace value on either column where you want to display the output, put some placeholders and then change the code to look somewhat like this:
Table.ReplaceValue(#”Changed Type”, each [A],each [A]/[B],Replacer.ReplaceValue,{“A”})
if this was DAX, the literal translation would be somewhat of the combination of FILTER(ALL(Table), my new filter context)
Here’s a full example on how to solve this scenario 99% of just the UI and some tweaking at the formular bar level:
let
Source = Table.FromRecords({[A=12,B=1], [A=24,B=2], [A=5,B=1], [A=12,B=3], [A=1,B=1]}),
#”Changed Type” = Table.TransformColumnTypes(Source,{{“A”, Int64.Type}, {“B”, Int64.Type}}),
#”Replaced Value” = Table.ReplaceValue(#”Changed Type”, each [A],each [A]/[B],Replacer.ReplaceValue,{“A”})
in
#”Replaced Value”
keep up the good content!
What a great comment, Miguel! Thanks for additional method!
By the way, there is no need to pass arguments explicitly, when you use default splitting character. So you don’t need “each … _” syntax.
Use function name only, like this:
fPowerTrim
Instead of this:
each fPowerTrim ( _ )
Hey!
Under that same note, where you say:
“Shortly: column is a list, we want to change “each element of list, “_” is a reference to element of list (this is how it can be used in argument of function). Details on pages 62 (§6.4.2) and 92 (§9.7) of M Language Reference (see PDF there).”
If you wanted to reference a position of the list, you’d need to use the curly brackets and the row # reference like this MyList{0} which would translate into give me the first element of data from my list.
The underscore is actually used to access the “current record” data. Commonly used as described in the pdf to showcase examples like for each _ do _[A] * 12
translated into plain english that means, from every record, grab the value from the column A and multiply it by 12. So the underscore is used towards records, but some people actually mix this element with a List so they wrap the underscore between curly brackets {_} to do lazy evaluation. The result would be a list of records, but I’m glad that you’re touching these topics.
As Sergey says, you for the way that you’re using the Ken’s function, you only need to write the name of the function and not insert any parameters.
Hello Miguel, thanks for additional clarification! I was afraid to mislead readers by mentioning records and list elements in the same time. Don’t know what would be the best explanation… Maybe English natives think differently.
From my point of view, when we transform a column we work with a list (list of values), no need to mix this with records, it is more general object. By “reference” I meant special reference for “each” keyword, not a general reference to list element like MyList{0}.
Hi Sergey, glad to see you here! I did it specially for those who don’t know shortcuts, for better understanding what happens. Users can be confused when they will try to apply function with several arguments, where “each” and “_” will be mandatory.
Hi Ivan,
Thank you for posting!
I will ask you about a problem.
I want to do a custom function that inserting “table, column1, value 1 and column 2” returns the max value in column 2 (it have to be a number column), and filters column 1 on value 1.
My problem is in Table.SelectRows, “Column1=Value1” seems dont be in the correct way.
= (Table as table, column1 as text, value1 as text, column2 as text)=>
let
maxValor = List.Max(
Table.Column(
Table.SelectRows(Table, each [column1] = value1
)
,column2
)
)
in
maxValor
Could you help me?
Thanks!
I would like to add,
the “List.AllTrue” is important becouse I am thinking to complicate the logic filter in the future.
Thank you from now, your page has really helped me to improve my queries in M!
Hi Matias,
Try following function
fx = (Table as table, column1 as text, value1 as text, column2 as text)=>
let
maxValor = List.Max(
Table.Column(
Table.SelectRows(Table, Expression.Evaluate( “each [” & column1 & “] = “”” & value1 & “””” ) )
, column2
)
)
in
maxValor,
When you pass Column Name as a text parameter, you can’t use it just like [ColumnName].
You need to build a string and Expression.Evaluate helps with evaluating it.
BR,
Ivan
It works perfectly!
Thank you very much.
//alternate approach (Record.Field is cleaner; let expression is redundant)
Fx = (Table as table, Column1 as text, Value1 as text, Column2 as text) =>
List.Max(Table.SelectRows(Table, each Record.Field(_, Column1) = Value), Column2)
//oops. Forgot the “1”
Fx = (Table as table, Column1 as text, Value1 as text, Column2 as text) =>
List.Max(Table.SelectRows(Table, each Record.Field(_, Column1) = Value1), Column2)
//ugh! what happens when you fail to test :/
Fx = (Table as table, Column1 as text, Value1 as any, Column2 as text) as any =>
List.Max(Table.Column(Table.SelectRows(Table, each Record.Field(_, Column1) = Value1), Column2)),
Thanks for sharing Steve! From performance point of view, it is probably better than Expression.Evaluate.