Excel Like TRIM Function in Power Query

[@joelabacherli1310]

I’ve scrolled past this video for a while. Finally checked it out and I wish I would have earlier. This is incredibly helpful to format the messed up data I get every day. Thank you so much as always. You are awesome.

[@Milhouse77BS]

Thanks! Another super video. I've been doing Power Query since Data Explorer, and I'm still learning from your videos.

[@GoodlyChandeep]

Glad to hear! Thank you for the tip

[@Sumanth1601]

Very practical solution..Had this exact issue last month.. Replicating the function to all columns was genius :) learnt something new today..

[@GoodlyChandeep]

Glad it was useful !

[@dc2guy2]

Same here.. my initial thought was to use Regular Expressions (RegEx) but wasn't sure if there was a way to do this in PQ

[@jameszhou162]

You read my mind Chandeep! Was thinking about scalability of this custom function. This is perfectly ✅done. Thanks a million

[@GoodlyChandeep]

Glad to hear!

[@olegariocamara9308]

Dude, this video is THEEEE BEST, helped me with so many problems I was having today! GREAT WORK !!!

[@amitk1208]

The way you explain is amazing one can easily understand even complex things . Excel is fun , laila gharani and ur KZbin channel are very helpful , keep it up

[@GoodlyChandeep]

Thanks Amit!

[@dianamgdata]

Never saw a function creation in Power Query before! I have to try it out in my reports!

[@FsoOmar]

Thank you Chandeep, your channel is my no. 1 source for learning for free. I guess this video is a duplicate of another one labeled "Remove unwanted spaces"! But thank you again and again. 😊

[@luisjavier1284]

It is pretty awesome. I came across with your channel two days ago and I am really excited about all the thing that I have learned. Kudos

[@SalihKocaSK]

Thanks for another very useful and helpful content @Chandeep. Would you think to include inseparable space (which is very common in copied text from browsers) and line break characters next time? It was my challenge last week, somehow I sorted it out but I am sure that you have simpler and more sophisticated solutions.

[@SJV992]

Hey thanks for this Goodly, it's a really helpful video and just what I needed. Your presentation style and clear explanations are great. Keep up the great work.

[@gennarocimmino]

what to say? simply brilliant. Very useful functions and approaches explained in really simple language. Thank you for sharing these tricks. see you next time.

[@adrianoschwenkberg6773]

A great and smart Solution using the given capabilities of M-Functions.

[@kadusilva4516]

Muito obrigado pelo vídeo! Gostei muito!!!

[@prasadraja17]

This is awesome! And I have a doubt to change the date range of an SQL query embedded into excel power query, as in for now I am changing it manually as I need to pull the details on incremental

[@williamarthur4801]

Thank you again for another interesting video, it was great that you went on to make a function, which I still find tricky at times, especially if there are multiple columns to work on, not so bad if it works on a table.

[@khalidmajeed2886]

great sir ................................Mashallah

[@laurentmarc1928]

Really impressive ! Seems so easy, but for anybody it would require hours of work. Thx a lot for this nice tip

[@GoodlyChandeep]

You are welcome!

[@SeyPras.]

Hi Chandeep, great vid. Is there a way to remove duplicate in the list before the text combine?

[@RonDavidowicz]

Very useful video, thank you. Is there a way of creating a library of these personal functions and use them in different queries, something like the personal macro workbook for Excel?

[@roywilson9580]

Thanks for posting this useful video. As always you explain things so clearly. What I learnt here will certainly be useful in the future.

[@GoodlyChandeep]

Great to hear!

[@syedaneesdurez9880]

Thank you very much for the amazing solution.

[@GoodlyChandeep]

Always welcome

[@nishantkumar9570]

Awesome 👌 You took care of scalability very well

[@nishantkumar9570]

If we need to apply the function on some of selected columns, do we need to provide those columns name in { }?

[@GoodlyChandeep]

To omit a few columns from the transformations, in the curly braces you'd write something like this. { {"ColumnName", each _}, {"ColumnName2", each _} }

[@raimundojs9547]

Fantastic! I had no idea we could apply a function to all columns like that. Thank you a lot!

[@incoref]

Thank you, it helped me to solve my problem beautifully!

[@s961201]

You Are awsome! Thanks a lot! One question though: I get confused as how a field/column sometimes is indicated by { } as in the last example in this video ({ } without any value means all columns) and sometimes by [ ] as in using the gear/dialogue box for the code (then your column name is between [ ]). Care to enlighten me…? Thanks

[@HachiAdachi]

I'm going to feel sMarter all day today because I spent 12 minutes watching this video this morning. Thank you, Chandeep! 🙏

[@GoodlyChandeep]

Cool!

[@AAAExcel]

Last trick is really amazing - thank you 🙏

[@GoodlyChandeep]

You're welcome!

[@izzatkiswani]

after this brilliant content ,the question why Microsoft make Trim function in PQ not as in excel !!!!! Thanks Chandeep !

[@obaidulbau]

Thanks for the incredible video. Please keep up the great works.

[@GoodlyChandeep]

Thanks, will do!

[@IvanCortinas_ES]

Excellent explanation, as always. It is very important to learn how to correctly create custom functions.

[@GoodlyChandeep]

Glad you like it!

[@wayneedmondson1065]

Thanks Chandeep!! Another great video full of useful tricks and tips. Will keep this technique at my fingertips for the future. Thumbs up!!

[@GoodlyChandeep]

Great!

[@wayneedmondson1065]

@@GoodlyChandeep Here is something I created adding a custom column using List.Accumuate. Also gets the job done: Text.Trim( List.Accumulate( {0..Text.Length([Text])}, [Text], (state,current)=> Text.Replace( state," "," ") ) )

[@mukeshbehera4499]

thanks for your tricks, awesome. One issue as while added a new column to excel source table, data model is getting failed. Is there anyway to solve this.

[@vamshimedishetty5624]

I appreciate your hard work. I followed the same and showing as list. Affer clicking on each item i.e., it is not showing as list. Just showing as normal string instead of line by line in list. What might be the reason.

[@billgillaspey9036]

Thank You !!! Another great lesson on the creative use of PQ to solve a problem that can sneak up and "bite" the unsuspecting power query user. (Ask me how I know... ha ha ). Just wondering: wouldn't there be a creative use of replace (i.e., Table.ReplaceValue to replace 2 spaces with single space (even if it had to be repeated) )?

[@yousefabdelsamie5983]

Excellent, Thank you very much. It would have been even better if you left the code in the description or something.

[@comsavvy_ogp]

Thanks for this amazing video! It's really helpful ❤

[@joaquimcosta952]

Very good solution....

[@ThGrum]

I'm addicted to your videos.😋

[@GoodlyChandeep]

That's what I'd like to hear. Thank you ❤️

[@MrAbrandao]

Great work. I find power Query misleading. The assist for the function table.transformcolumns doesnt show all options from the start, you have to type {} for it to show the next availeable option

[@advrohitowhal9794]

Excellent Boss!! Thanks Bro!!!

[@GoodlyChandeep]

You're welcome!

[@ricardobunge5957]

Always fantastic stuff, Chandeep! Thanks again.

[@GoodlyChandeep]

Glad to hear that!

[@AnbarasuAnnamalai]

Much useful in cleaning the data, Thank you very much

[@GoodlyChandeep]

Glad to hear that

[@abdullahquhtani4247]

Great 👍🏼. I have two questions please! 1- Is it possible to use for each to go through all columns instead of using table transform?! 2- Is there a solution to do the opposite, in case I want to add spaces between merged words?

[@vishnuvardanatmakuri]

For q2. If u r able to split the merged word based on some delimiter then it's just following merge function to recreate the sentence w spaces

[@priyeshsanghvi8424]

You can try Text.Insert function

[@ivancuadros8731]

Really good tips, saved the best for last!

[@GoodlyChandeep]

Thanks!

[@ronnieyandell931]

An excellent video. Thanks

[@martyc5674]

Excellent as usual Chandeep 👍 👍

[@GoodlyChandeep]

Thanks a ton

[@excel-in-g]

{ } to apply on all columns... Awesome, indeed! Gonna steel that 🙂

[@EricaDyson]

As you said, that is AWESOME!

[@muhnuur]

It's a great Solution... Awsome.. !

[@GoodlyChandeep]

Thanks !

[@ratiram9556]

Great very useful trick . Thanks.

[@GoodlyChandeep]

Glad you liked it!

[@karimallahwala7022]

Very interesting and helpful Video. thank you very much for sharing

[@GoodlyChandeep]

Glad you like it !

[@davidfelipepenaguardiola1177]

Pretty awesome! ;) Thanks so much. Very usefull solution

[@GoodlyChandeep]

Glad you like it!

[@hoaithuongluu2869]

That's awesome ❤❤❤ thanks so much bro

[@ernstborgener3745]

If the table contains columns with numeric values or dates, ERROR data is displayed. A small change to the RemoveSpaces function prevents this. (InputData as any) => let CleanData = try Text.Combine(List.Select(Text.Split (Text.Trim(InputData)," "),each _ "")," ") otherwise InputData in CleanData

[@ShubhamSharma-ls6hj]

Thanks brother. Do you have solution to convert ranges of different excel sheet into table in one go.

[@gustavobarbosa906]

The possibility of using empty curly braces to transform all the columns of a table is invaluable. Thank you, for teaching us how to do it!

[@KuldeepSingh-nq1vi]

Very Nice Video as well as Nicely Explained each and every thing clearly..

[@GoodlyChandeep]

Thank You!

[@randolfojolongutierrez5311]

Belleza!!!

[@GoodlyChandeep]

Thanks!

[@navisalomi]

Awesome. Your contents are wonderful.

[@GoodlyChandeep]

Thanks!

[@Luciano_mp]

Lol....Excel is fun... Very good man!!!! Thank you!

[@GoodlyChandeep]

Glad you enjoyed it!

[@incoref]

Thank you!

[@robertlevak]

YEAH! This is cool. Thank you so much!

[@MANATTOWN]

Chandeep I have another approach to this problem. First replace the " " with " |" then replace the "| " with "" (null) and lastly replace the "|" with "" (null) again. I hope this will work.

[@txreal2]

That is awesome!

[@z.719]

Very useful function! Thank you Sir.

[@GoodlyChandeep]

Glad you like it

[@Ypurenete]

Espetacular!!

[@melissagenthner2705]

How would you use this function for few selected columns?

[@lukereds9975]

Hi Chan vedy good videos than you, in this one you could avoid to use Text.Trim because the spaces will be deleted from the other formulas.

[@faizanahmed2239]

Hi dear, i tried this approach, but space not removed by list.select. can you confirm what would be the issue. Is this related other characters???

[@rohit2492]

This was really helpful, however, can you also make a similar query if we have both data and numbers?

[@FRANKWHITE1996]

Thanks for sharing ❤

[@GoodlyChandeep]

Glad you enjoyed it!

[@FRANKWHITE1996]

@@GoodlyChandeep always🙌

[@alphabarry3448]

Hello thank you for this video, I applied it but in the Text.Select it generate error. In the column though it is text type there are numbers as well. Thank you helping find what os wrong.

[@tigitang8719]

You're selecting the text for the list, therefore, you need to use List.Select, not Text.Select.

[@modernboutique1389]

Another great learning

[@GoodlyChandeep]

Glad it was helpful!

[@modernboutique1389]

@@GoodlyChandeep i sent you text on LinkedIn from Harmeet Singh

[@ranjitramakrishnan6823]

Hi Chandeep, you are amazing👍

[@GoodlyChandeep]

Hey, thanks!

[@tnssajivasudevan1601]

Thank 😊, very interesting

[@GoodlyChandeep]

Glad you liked it!

[@agustinrajan8621]

God level 🙏🙏.. Really loved it.

[@GoodlyChandeep]

Thank you !

[@ExcelWithChris]

It must be terrible to be so clever!! LOL Thanks from South Africa.

[@GoodlyChandeep]

Glad you enjoyed it!

[@telclissonperes]

Bruxaria! 🙂

[@rifkagumay]

Terima kasih.

[@GoodlyChandeep]

thank you so much

[@qungu7251]

Thank you, I come across an error when I use the function, An error occurred in the ‘’ query. Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression? Can you please help me to solve this error? thank you

[@jerrydellasala7643]

AMAZING! How do I give a dozen Likes? Could the function be changed so that just a column is provided as the permeameter instead of the whole table?

[@GoodlyChandeep]

Thanks! Your 1 like worth a Dozen 💚

[@balajikomma541]

kindly make video on Evaluate DAX function

[@santoshpv321]

This is godly! Super o super....

[@GoodlyChandeep]

Thank you!

[@equilibriocontabilidadeadm5277]

Great Video

[@GoodlyChandeep]

Glad you enjoyed it

[@MdMehediHasanMuttaki]

Bro i have data in excel file, sheet name xyz, now if i change sheet name abc then the will load to power query?

[@abhijeetshetye3764]

Amazing 👌🏻

[@GoodlyChandeep]

Thank you!

[@julie_chen]

👏 Awesome

[@GoodlyChandeep]

Thank you!

[@surajchavan6447]

Awesome 😎😎👍🏻

[@GoodlyChandeep]

Thanks ✌️

[@faisalag9611]

Awesome.

[@GoodlyChandeep]

Thanks!

[@DhruvDua88]

Brilliant

[@GoodlyChandeep]

Thanks!

[@PawelNap]

👍

[@williamthedataprof]

Thanks bro. That last tip was genius. I've adopted your solution but removed the Trim part of the function. Any harm? William. (InputText as text) => let TrimmedText = Text.Combine( List.Select( Text.Split( InputText, " " ), each _ "" ), " " ) in TrimmedText

[@tigitang8719]

Text.Trim function removes leading and trailing spaces.

[@rajendrakumaroram9812]

Amazing👍👏👏👏

[@GoodlyChandeep]

Thanks!

[@brij26579]

👌👌

[@GoodlyChandeep]

Thanks!

[@dandeman1148]

When I load Names with Spaces into PQ they seem to be already trimmed, although when Close and Load hey aren't. Is there a setting which automatically shows them as trimmed even if they are not One other thing for the last part it causes an Error if you RemoveSpace Function for all if it has a Date Column. How can you add step to select certain columns :)

[@GoodlyChandeep]

I don't know of any such setting. You could be dealing with something that looks like a space but isn't.

[@tigitang8719]

You can omit the Date column for the formula to avoid getting errors.

[@sebastiannorge2980]

Thanks

[@GoodlyChandeep]

Glad you like it!

[@Rice0987]

I like how you giggle on results you get. 😁👌

[@GoodlyChandeep]

he he he.. Did I? I went back and saw the whole video again 😂

[@Rice0987]

@@GoodlyChandeep yeah, you always do that and this is cute, don't stop it, it's your feature😁👌

[@GoodlyChandeep]

@@Rice0987 Still couldn't figure out where I did it :D

[@Rice0987]

@@GoodlyChandeep read the first comment: when you get result. And i understand, why you act so, cause power query is amazing instrument to get results in couple steps, when before you should make several complex manipulation. That's why you gig.😊

[@ashivns]

How to deal with null in columns

[@sujit3375]

Is this there in power query course

[@GoodlyChandeep]

No I am working on creating a new course on M language.

[@sujit3375]

@@GoodlyChandeep please do it asap Its HIGHLY needed by all PBI developer