Remove extra spaces behind names

[RULIANA]

I have this list of machines, where some have a group of spaces behind the names, and I'm trying to use this list in Power BI and merge it with another dataset. I tried to used find/replace to remove this but it also removes the 0 before each name, which I need for a successful merge. This also removes the spaces between the names which i need to keep in , ie ASSY 01. Is there another way to do this besides re-writing all of the names?

MACHINE
0364
0551
0118
0130
0104
0111
0117
ASSY 01
0128

[#2 · Accepted solution]

Hello @RULIANA ,

You can use the Trim command under the Format drop-down on the Transform ribbon. That will remove leading and trailing spaces, but will keep the data type as Text and will retain the leading 0 and the space in the middle of the text.

The screenshot is from the Power Query editor in Excel, but it works the same in Power BI.

[#3]

@RULIANA 

Once you import the data in Power Query Editor, to remove any leading or trailing spaces from a column, you may also right click the column header and choose Transform and in there you can select Trim/Clean your data.

Since your column contains both the numeric values and the text values, it will not apply the Changed Type step to change the datatype of the column automatically but somehow if it does, you can remove the last step Changed Type.