Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I am using the Excel ( Query Editor) which uses the power query language.
Here is my challenge. I have a column that has data as follows:
Column Name is "Name"
Data in the Column
, David, Will, Bill
, John, Joe
David, Hillary
John, Billy
, Bill, Harry
Bill
The ones that begin with a comma how would I go about removing the leading "comma" and "space" from the string. Thank you very much. I do not want to remove the other commas in the middle of the string. Thank you very much.
Solved! Go to Solution.
Hi @dmcbrier,
If all the rows contain the ", ", then we could take use of split column(By number of Characters), then delete the ", " part.
If the name column contains row that are without the ", " heading, then we could add a custom column, with the following function:
Text.TrimStart
to remove the ", " head string.
For example:
After the custom column added, you may choose to delete the original column in Advanced Editor.
Regards,
Michael
I have followed the suggested solution below but I am receiving an error. My issue is slightly different, I am trying to remove the characters "0-" when ever they occur at the front of values in a column.
When I try the formula with only "0" it will remove the leading zero, when I try with "0-" the words error are displayed in the column.
I would appreciate any suggestions to fix please.
Hi @dmcbrier,
If all the rows contain the ", ", then we could take use of split column(By number of Characters), then delete the ", " part.
If the name column contains row that are without the ", " heading, then we could add a custom column, with the following function:
Text.TrimStart
to remove the ", " head string.
For example:
After the custom column added, you may choose to delete the original column in Advanced Editor.
Regards,
Michael
Hello, I just tried this as I have a similar situation just at the end so I used TrimEnd with the same column formula shown. Unfortunately it does not work:
What am I doing wrong?
Hi, Can we combine two values here? For example I want to trim all leading "," and ",," because I have values beginning with comma and other with two commas.
Michael,
Thank you very much for the time and effort. This worked absolutely brilliantly. Cheers!
User | Count |
---|---|
134 | |
74 | |
72 | |
58 | |
54 |
User | Count |
---|---|
194 | |
95 | |
65 | |
62 | |
53 |