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

Grasshopper: Voronoi Holes on a Planar surface

By Published on:
One of the best natural examples for a Voronoi is the Wing structure of a dragon fly.Grasshopper can help us to build a shape like this in Rhino. Image Courtesy:  The Pond blog Grasshopper by default has a node for Voronoi, we just have to prepare the necessary information to feed into the node. The following example shows how to create a Voronoi shaped holes in a surface Create a planar  Surface  from rhino or grasshopper Randomly Populate the surface with points using the  Populate 2D  Node Feed the surface and the points into the  Voronoi  node and feed a number slider too to adjust eh radius This forms the required Voronoi structure which you can edit the number of points or radius. To create the holes we need to offset each and every inner portion towards its centre using the  Scale  node We can find the centre of each of the inner portions using Area node I'm not sure why but using the offset command su...
Post a Comment
Read more

Grasshopper: offsetting curves on surface vs scaling curves on surface

By Published on:
Though in Grasshopper Offset and Scale are two different node functions, when it comes to a planar surface, the results of the both are similar. But, I found the " Scale" node to be effective when you want to loft the resultant curve with another. The following images show the different results of a Voronoi structure that yielded when scale and offset nodes are used. When the Offset node is used When Scale is used
Post a Comment
Read more

Create a shortcut to open Screen Sketch in Windows Ink Workspace

By Published on:
    With the addition of Windows Ink Workspace in Windows 10, we can now take screenshots and draw over the screenshot easily. You can simply click on the Windows Ink Workspace button in the taskbar or press Win+W shortcut to open the Windows Ink Workspace and click on "Screen Sketch" to take a screenshot and start editing it.    But if you would like to have a shortcut to open screen sketch directly without having to open the Ink workspace, you can create one manually using the following simple steps as suggested in the following link : Go to your PC Desktop Right Click and select New > Shortcut In the Next window that appears, copy and paste the following text and click Next ms-penworkspace://Capture Choose a name for the shortcut in the next window and click finish Now, right-click on the shortcut that is created on the desktop and select Properties. Go to Web document tab in the properties window and choose your desired shortcut in the "shortcut ke...
Post a Comment
Read more