Skip to main content

Power Query - "SUBSTITUTE" Function: Remove/Replace multiple values in a column in One Step.

Image
By Published on:
Often times, when you get data from a source, there will most probably be something incorrect that needs to be fixed using an ETL process of Power Query. And most of those transformations would require either Replacing or Removing string's that are not necessary.

For example, if you extract any financial data from a source which has multiple currency symbols, there's a chance that the numbers get extracted along with the currency symbols like "$700"/"€700" instead of 700. And these type of cases makes it tough to Transform the Data to our wish as the value will be considered as a text instead of a number.

In those type of cases, we would most likely have to create multiple steps to remove the multiple currency symbols from the Column. But thanks to the solution provided by Marcel for one of the questions posted on Stack Overflow, with a little manipulation of the Query language (M), we can replace/Remove multiple strings in one step using the List. Accumulate function.

Remove Multiple Strings:

Below is a sample code that you can use to remove all the strings/text fields highlighted in blue from any column.

= Table.TransformColumns(#"Previous Step",{{"Column Header", each List.Accumulate(Text.ToList("⬣£€$"),_,(String,Remove) => Text.Replace(String,Remove,""))}})

You can copy-paste the above code in your query and replace the highlighted text to match your query.

Replace Multiple Strings:

Below is a sample code that you can use to replace all the strings/text fields highlighted in blue from any column to the text highlighted in Red.

= Table.TransformColumns(#"Previous Step",{{"Column Header", each List.Accumulate({{"$","a"},{"¥","b"},{"¢","c"},{"£","d"}},_,(string,replace) => Text.Replace(string,replace{0},replace{1}))}})

You can copy-paste the above code in your query and replace the highlighted text to match your query.

P.S: 

  1. You can check out the following video from Miguel on how to create a custom function that does the same thing. Although, the Syntax that he uses is for Advanced users.
  2. Also, you can also have a look at the following video by Marcel where he explains the List.Accumulate function.


Labels:

Comments

Post a Comment

Popular Posts

Scribble on your PC/Mac screen with these annotation tools

By Published on:
     While you are in a presentation or hosting a webinar, you might want to write/highlight a part of your screen to draw the attention of your audience on something important. Although you can use the tools from powerpoint to scribble in the PPT file, you might want to write something on other apps as well.      That's where these awesome apps come into play. Although these apps have similar features, you might prefer one above the other. ZoomIt :       ZoomIt is an application developed by Microsoft which can be installed from the following link . It is a free tool with features like Text, Shapes, whiteboard, blackboard, zoom . When activated, the tool converts the current screen into a static image and allows you to draw/write on it. One the tool is activated, you can also choose to create a whiteboard/blackboard screen on your PC to draw on. Although the colors are limited to 6 colors: Red (R) , Green (G) , Blue (B) , Orange (O) , Yellow (Y) , Pink (P) , it should be
Post a Comment
Read more

Change the orientation of Snap Grid in Revit.(Rotate Grid/UCS)(09 of 10)

By Published on:
One of the shortcomings of Revit is that unlike AutoCAD you can't save multiple UCS to rotate the snap grid based on the orientation of the building. But we can make use of the grid from the workplane to align/snap the elements in whichever angle you want. To see and manipulate the grid in any of the plan views, Go to the plan view. Click on "Show"  button in the "Workplane" toolbar of "Architecture" tab in the ribbon. The grid by default will show up aligned to the screen orientation. You can select the grid and click on rotate to rotate the grid to your desired orientation. Snap Grid
Post a Comment
Read more

Moving Spotlight effect in PowerPoint using default tools.

By Published on:
You might have seen moving spotlights in a lot of videos. How cool would it be if you can do that in Powerpoint for one of your presentations in School/Office? The above GIF is a fast-forwarded recording of PowerPoint slides. You can replicate this effect using a combination of Crop and the Transitions feature of the powerpoint. Below is a video which explains that process to achieve the above result.
Post a Comment
Read more