Table.TransformColumns – alternative in PowerBI and PowerQuery in Excel

This article shows a trick for a little problem that annoyed me for quite some time: How to get Table.TransformColumns transforming the values of a column with a reference to a value (from the same row) of another column?

1 Replace text by a value from another column

So instead of adding a new column where the “*” is replaced by the value from column “WhildcardValue”, I just want to perform the replacement-operation in the original “Text”-column, so that I don’t have to rename and delete the other columns later:

So far, I always ended up fighting with Table.TransformColumns-function and got quite frustrated because I couldn’t find a way to reference the (row-) value of the other column. As it turns out, I was fighting the wrong target here, because Table.ReplaceValue is actually the saviour for this challenge:

1.1 Check column “Text” -> Transform -> Replace Values

So in (4) we just filled in a dummy-value, which we’re going to replace with a reference to the desired column: “each [WildcardValue]” like this:

1.2 Edit the generated function code in the formula editor:

Code: = Table.ReplaceValue(#"Replaced Value1", "*", each [WildcardValue], Replacer.ReplaceText, {"Text"})

That’s cool.

But we can top it:

2 Divide [Number1] by [Number2]:

2.1 Again: Check column “Number1” -> Transform -> Replace Values, but fill in dummies for both:

2.2 Edit 2 fields in the formula editor:

Watch the 2nd replacement: We just need one “each” at the beginning of the expression, then the references to the columns just need the square brackets like

Code: = Table.ReplaceValue(#"Replaced Value1", each [Number1], each [Number1]/[Number2], Replacer.ReplaceValue, {"Number1"})

One thing to mention: This command is sensitive to the type of the column where it is called from. So in the first example, where we called it from a text-column, the 4th argument of the function has automatically been set to “Replacer.ReplaceText”. But in the 2nd example it is call called from a number-column and there it has been set to “Replacer.ReplaceValue”. This has served us well here, but can lead to unexpected errors in some other cases: When you want to replace the values of a text-column with a value from a number-column, you have to wrap the reference to the number-column into a “Text.From(…)”, to transform the number-value into a text-string.

Edit: … and here we go again: This trick was not new, one just need to search before posting 😉 : https://bondarenkoivan.wordpress.com/2016/12/12/transform-column-using-custom-function-in-power-query-part-2/ (in Miguel Escobars comment below)

Enjoy & stay queryious 😉

Comments (8) Write a comment

  1. Helpful article, thanks for sharing.

    With the Divide option – would you normally use

    = Table.AddColumn(PreviousStep, “New Column Name”, each [Item 1] / [Item 2], type number) ?

    Reply

    • Thanks Wyn!
      Personally, I’d end up without the type-definition at the end, because I’m always using the UI for this step and forget to transform there.
      But the definition for sure makes sense, as you don’t have to transform column types later.
      Actually, I whish that this type would be defined automatically for numbers, once one has done mathematical calculations.

      Reply

  2. Thanks. I had a same problem many times, and my solution was to use AddColumns, although I knew about Table.ReplaceValue function. I was blinded with the old habit. There is also a function Table.ReplaceMatchingRows which could come handy if you wish to fix several columns at once.

    Reply

    • Thank you & yes: We still miss the transformation for multiple columns.
      Table.TableReplaceMatchingRows seems to require a match-definition of all columns, or am I mistaken here?

      Reply

  3. Pingback: #Excel Super Links #107 – shared by David Hager | Excel For You

  4. A problem with Table.ReplaceValue is after the transformation all columns change data type to any – I wonder what is the logic behind that

    Reply

Leave a Reply