In regular Excel, all the above Power Query transformations can be done with simple functions, except for TRIM which Power Query does it slightly different from the regular Excel. You may read the previous post for more details.
You may see the corresponding Excel functions in the following table:
Power Query
Regular Excel function
- lowercase
- UPPERCASE
- Capitalize Each Word
- Trim (only remove leading and trailing spaces)
- Clean
- Add Prefix
- Add Suffix
- LOWER
- UPPER
- PROPER
- TRIM (also remove extra spaces between words)
- CLEAN
- “Prefix” &
- & “Suffix”
You probably know all these Excel functions and wonder why we need to do it in Power Query. I had the same questions too when I first started with Power Query. If you try to clean / transform your data in Power Query, you will find it makes perfect sense NOT to do it with regular Excel functions on worksheet. We shall do all sorts of transformations in Power Query Editor. And most of the time, text transformations stated above are only part of the data cleansing that we need. Therefore, it is important for beginners of Power Query to be aware of their existence in Power Query. And the best thing is we, as users, can do it through merely the User Interface of Power Query Editor, i.e. with a few clicks.
Let’s watch it in action how we can do these text transformations with Power Query:
wmfexcel 