Cleaning Data in Query Editor with Inconsistent Delimeters

[Anonymous]

Hi All,

I am trying to clean some data from within the Query Editor, but I'm a little limited in my M knowledge to be able to do so. Here's a mock data set that I am working with: 

What I am trying to do is concatenate the Project Number and Project Name columns into a new column that would look like
"Project Number" - "Project Name". For example, the first row would eventually look like the following in a new column:

237643 - General Overview - Year End - JAN 2020

However, this particular data is manually entered in the backend, and sometimes human errors appear. The most common is an accidental additional space before the project name (seen in row 2 and row 5). I am trying to clean the Project Name column so that all of these errorneous first spaces are removed, so that when I create the new column everything will be consistently spaced and formatted.

Currently, I tried to use the native functions present in the Query Editor to create a new column that only returns the text after a first space delimeter (the Extract "Text After Delimeter" function using a "space" as the first delimeter):

But this removes the first part of the Project Name for the projects that don't have this extra space. Check out this screenshot, Rows 2 and 5 are correctly fixed, but using this out-of-the-box feature when creating this new column gets rid of the first word in the other cells that were correctly formatted:

As I mentioned before, I'm not well versed in Power Query M, but my suspicion is that something needs to be tweaked in this forumla. In layman's terms, I need help creating a forumla that only removes a space if it exists before the first character shows up. If there is not space before the first character, then just return the whole Project Name. 

I hope this is enough context to help resolve this issue! I appreciate any help in aiding me understanding M a little bit more. Thank  you!

**EDIT: Thank you all for the initial insights about using the Trim function. I have attempted to use that, but for whatever reason, it does not remove those occasional leading spaces. Is there a formulaic solution in Power Query M that might work?

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can use the Text.TrimStart function to meet your requirement, this function can removing all leading whitespace from text value. You can get more information from this document: https://docs.microsoft.com/en-us/powerquery-m/text-trimstart

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jdbuchanan71]

Hello @Anonymous 

In the query editor, if you right click on the column header you can go to
Transform > Trim 

This will remove leading and trailing spaces from the column for you.

https://docs.microsoft.com/en-us/powerquery-m/text-trim

You might also want to hit it with a Clean as well, just to be safe.

https://docs.microsoft.com/en-us/powerquery-m/text-clean

[Nathaniel_C]

@Anonymous ,

Use trim on your column to remove whitespaces.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel