Transform Column using custom function in Power Query (part 2)

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:

  1. We need to Trim column [Name]
  2. 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

121016_1725_TransformCo1.png

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! 🙂

Advertisements

8 thoughts on “Transform Column using custom function in Power Query (part 2)

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

    • Ivan Bondarenko 2016-12-13 / 14:35

      What a great comment, Miguel! Thanks for additional method!

  2. SergeyLossev 2016-12-14 / 06:51

    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.

      • Ivan Bondarenko 2016-12-16 / 21:15

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

    • Ivan Bondarenko 2016-12-16 / 21:06

      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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s