Power Query helps you to transform your data, there are various in-built Power Query M code function that can be useful to transform you data as per you requirements and of them is adding Prefix and Suffix to values in column.
As you can see, here we have one sample data.
Adding Prefix
Now, assume that you want to add a specified value to the front of each values in TranCode. The type of TranCode column is text.
Let’s add a prefix Tran_to values in column TranCode.
Go to Power Query Editor, click on Transform tab then select TranCode column after that select Add Prefix option from Format tab.
Once you select Add Prefix option, a pop up window appears. Now you need to provide a value that you want to add as Prefix to values in TranCode column.
Let’s give it a value Tran_ and click on OK button.
Now, you can see a Prefix Tran_ is added to each values in TranCode column.
Adding Suffix
Now, let’s say you want to add a specified value to the end of each values in TranCode.
Let’s add a prefix _Tran to values in column TranCode.
Repeat above steps again but this time select Add Suffix option.
Once you click on Add Suffix, a pop up window appears. Now you need to provide the value that you want to add to the end of each values in column TranCode.
After that, click on OK button.
Now, you can see a value _Tran code is added at the end of each values in TranCode column.
So far, we have seen how to add a prefix and suffix to a text values.
Now let’s see what happens when we use Add Prefix or Suffix options to a numeric value.
As you can see the type of TranId column is numeric.
Let’s add a Prefix 0 to the front of each values in TranId column.
Now, provide the value that you want to add to the front of each value after that click on OK button.
Now you can see, it adds a prefix 0 to each values in TranId column that is fine but note that it also changes the type of TranId column from numeric to text.
Also Read..
Using IN operator in Power Query M code
Remove duplicate and keep latest record in table using Power Query
Replace multiple columns values in Power Query
147 total views, 1 views today