Sometimes we need to transform the text from a huge data table which is not directly possible. But, with Power Query, you can easily do it using Add Column from Examples feature.
Add Column From Examples By Power Query
Step 1– Select the column on which you want to apply changes
Suppose you have a data table as shown in below picture where you want to remove the delimiter ‘-‘ from each row and change the case of the text. Then, you have to first select that column for applying changes.
Step 2– Add Column from Examples using From Selection option
To add a new column in Power Query, go to –
Add Column > Column From Examples > From Selection
Step 3– Make changes to the selected column using Add Column from Examples
Power Query gives you access to edit the column you selected after clicking on From Selection option of Column From Examples button. You just have to replace the text in the first row as you want and the rest Power Query will do for you! As you can see in the below picture, Power Query generates a preview in the new column and you just have to click on OK if the preview is as per your expectation.
Step 4– Close & Apply
Click on Close and Apply button after you get the expected result as below.
You can learn more :
- Learn How to Concatenate Datausing ‘M’
- Steps to Replicating Excel’s Find Function With ‘M’
- Read How to Add Conditional Column in Power Query
- Top Ways to Import Data From Text in Power Query