When working with imported data in Excel from external sources, we often encounter text strings that begin with unwanted characters. These can be problematic for data analysis and need to be cleaned before we can make data-driven decisions.
In this post, under our “Data to Decisions” series, we’ll tackle a common issue: removing or replacing a specific starting character from a text string using Power Query.
Consider sample data from a web URL of NFL team game details that is loaded into Power Query:
Scenario Breakdown
We are required to perform the following operations on the VALUE column: If the text starts with “@” then remove that leading character and return the modified string else return the value corresponding to the TEAM column.
Power Query is to our rescue here!
Step 01:
Considering we need to replace the rows in the Value column with the corresponding values from the Team column, we use the M language functions.
First, let us create a custom column to store this new value, let us call it REPLACED NAME
Step 02:
The functions we need here are Text.StartsWith() which searches our string for the character we are looking for (here the “@”) and the Text.Range() function which is used to return a substring of the input string offsetting by a specified number.
So our M formula language function will be:
Our newly created column will be:
This way we can easily remove any given starting character in Power Query.
Do check our dedicated YouTube video on converting the NFL schedule grid data into a list of games here:
If you have any feedback or suggestions, please post them in the comments below.
