Replacing values in a table column using a custom function

[joel_h]

I'm new to Power BI and Power Query in Excel (right now I'm working in Excel 2016). When working with the Advanced Query Editor, I have learned I can invoke custom functions I've built in the M Query language, so they run on columns in a table and generate a new column that holds the results of the function. This would be using Add Column -> Invoke Custom Function.

However, is it possible to invoke a custom function on an existing row, which as I can invoke Format operations like Trim and Clean, without creating a new column?

See the image reference below that I hope explains my question a better...



Essentially I'm trying to understand if its possible for me to invoke a custom function I've written such as NormalizeWorkPhone, on a column, without going through the intermediate steps of running the function via Add Column -> Invoke Custom Function, which would add a new column (and where I then would have to remove the original column, if I just wanted to keep a single column based on the resulting values of my function).

 

[Matt Allington]

Re: Replacing values in a table column using a custom functino

I would never say never, however I Think it would be tough. Here are my thoughts. When you write a function you need to pass a starting object into the function. Normally this is the table that is displayed on the screen as at the previous step. If you wanted to return that same table that you start with, but with one column that has been transformed, you would need to have your function reproduce the other columns first and then transform the specific column too. This seems like a lot of work for no net final value.

However, each line of code in the advanced editor is just a standard function. You could complete the insert custom function step, thestep expand, the delete original column step, and then combine those three steps into a single step if you wanted to. The outcome would be identical however you would not see the interim steps appear in the applied steps pane on the right. Once again, a lot of effort for no real gain

 

[joel_h]

Re: Replacing values in a table column using a custom functino

Thanks Matt, that makes sense to me, I think. Just as you say, if this would involve working around how the query editor is built just to achieve some visual, the net gain seems questionable.

I'm still thinking if there might be a better, native way of transforming values in a column (without adding new columns) than using Add Column > Invoke Custom Function? (Given that transforming the values requires some kind custom function to calculate the new values, and assuming we are doing this in Excel 2016 and not desktop Power BI or other application.)

As I mentioned, I'm new to the new Query features in Excel and Power BI, so I don't now if I'm using best practices here or just doing this in a roundabout way, when there are easier ways already. Not that pulling out a new column for the calculated results is that hard; I'd just thought it made sense, as you can run other built-in transformations such as Replacing Values or Uppercase, without adding new columns during the operation.

 

[MarcelBeug]

Re: Replacing values in a table column using a custom functino

In such cases I always select some text column, choose some function on the Transform tab (usually trim) and adjust the generated code.
Video.

Note: this is only possible if you can do the transformation without using values from other columns; otherwise there is still an alternative.

 

[joel_h]

Re: Replacing values in a table column using a custom functino

Thanks a bunch Marcel, both those approaches are really useful to me!