Basic text transformation in #Excel #PowerQuery

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

  1. lowercase
  3. Capitalize Each Word
  4. Trim (only remove leading and trailing spaces)
  5. Clean
  6. Add Prefix
  7. Add Suffix
  1. LOWER
  2. UPPER
  4. TRIM (also remove extra spaces between words)
  5. CLEAN
  6. “Prefix” &
  7. & “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:


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Power Query. Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.