Trim column headers with spaces in between words in one step in Power Query

There is a trim function in Power Query to get rid of spaces at the beginning or end of a phrase, but unlike excel trim, it wont get rid of excess whitespaces between words.

In this video, I will show you how to get rid of all spaces no matter where they are on one step in power query.

Chapters:
00:00 Intro & explain use case
01:00 Demote headers & Transpose
01:20 Split the headers by spaces
02:00 Filter the spaces out
02:30 Concatenate the remaining words with a space
03:18 Now do combine all steps into one
Done!

Other column headers tricks:


SUBSCRIBE to learn more about Power and Excel BI!

Our PLAYLISTS:

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:


Many thanks in advance!

▲▲▲▲▲▲▲▲▲▲


************





************


QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Twitter ► @curbalen, @ruthpozuelo

#CURBAL #SUBSCRIBE

[chiragkhunt]

wow, what a useful trick! day-saving trick! thanks a lot!!

[vida]

Thanks Ruth for this great step by step explanation! Very nice logic behind all these steps!

[MilhouseBS]

Adding this to my toolbox of Power Query examples immediately!

[Amr-Ibrahim-AI]

This is really cool!
I like how you *always* make your tutorials simple and useful, Ruth.
Appreciated :)

[entertainmentgalaxy]

Nice way to explain with each step.
Thanks for sharing PQ fun.

[HarshNathani]

Great video. Thanks for the tip. Time to learn M now. :) . Your videos are always inspiring

[BansalBytesGaming]

Thanks again for great tutorial. Can you please make one video to add N business days to particular date?

[pharmacyholland]

Or an easy one, Drag hearders as first raw, change type to text, replace values (ctrl+H). Change first raw as headres again!

[joangarcia]

Prometo prometo y prometo que cuando venga a buscar este “one step” volveré a verte para entender como funciona. 🙂

[GaryMillerUK]

How can I use this on a column in my table? Your code does not include the name of the column.

[bishwarai]

Is there any way to disable the measures like we can disable the tables ?

[wanderleihuttel]

What an amazing tip Ruth!! Do you know if is it possible to bulk replace values from a list for nothing?

I would like to input a list with phrases and replace for nothing:
{ "SOME DESCRIPTION NUMBER", "ANOTHER DESCRITION ANOTHER NUMBER", "SOME ANOTHER DIFFERENT DESCRIPTION"}

Table before changes
ID|DESCRIPTION
1|SOME DESCRIPTION NUMBER 45 ANOTHER DESCRIPTION
2|ANOTHER DESCRITION ANOTHER NUMBER 8549 MORE DESCRIPTION
3|SOME ANOTHER DIFFERENT DESCRIPTION 45987 MORE DESCRIPTION

Table after changes
ID|DESCRIPTION
1|45 ANOTHER DESCRIPTION
2|8549 MORE DESCRIPTION
3|45987 MORE DESCRIPTION

[matsbsjogren]

Text.Combine({Text.Trim(Text.BeforeDelimiter([Column1], " ")),
Text.Trim(Text.AfterDelimiter([Column1], " "))}, " ")

[mccalabb]

Pretty cool One Go, but it fails in my excel query

= Table.TransformColumnNames(#"Desc", "Custom", each Text.Combine(List.Select(Text.Split([#"Desc"], " "), each [#"Desc"] <> ""), " "))
Expression.Error: We cannot convert the value "Custom" to type Function.
Details:
Value=Custom
Type=[Type]

Is it any option missing in the script

[GaryMillerUK]

Hi! At 2:24 you put an underscore before <>. Why is that, please?