Excel Like TRIM Function in Power Query

Подписаться 89 тыс.

Просмотров 24 тыс.

50% 1

Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
Here in this video, we will talk about how you can remove unwanted spaces from your text values in Power Query. We'll also learn about creating dynamic custom functions.
===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co.in/blog/
Corporate Training 👨‍🏫 - www.goodly.co.in/training/
Need my help on a Project 💻- www.goodly.co.in/consulting/
Download File - goodly.co.in/remove-spaces-be...
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - goodly.wordpress@gmail.com
===== CHAPTERS =====
0:00 Intro
0:21 Removing Spaces
4:28 Creating a Function
8:51 Applying to Multiple Columns
10:44 My Courses
===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers!
- - - - -
Music By: "After The Fall"
Track Name: "Tears Of Gaia"
Published by: Chill Out Records
- Source: goo.gl/fh3rEJ
Official After The Fall RU-vid Channel Below
ru-vid.com/show-UCGQE...
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)
Full license here: creativecommons.org/licenses

Наука

Опубликовано:

29 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 166

@joelabacherli1310 8 месяцев назад

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

@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.

@raimundojs9547 Год назад

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

@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!

@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 !

@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

@SJV992 3 месяца назад

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.

@olegariocamara9308 Год назад

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

@dianamgdata Год назад

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

@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.

@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.

@yousefabdelsamie5983 4 месяца назад

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

@SndfOmar 5 месяцев назад

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. 😊

@adrianoschwenkberg6773 11 месяцев назад

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

@amitk1208 Год назад

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

@GoodlyChandeep Год назад

Thanks Amit!

@comsavvy_ogp 4 месяца назад

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

@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!

@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!

@incoref Год назад

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

@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!

@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?

@IvanCortinas_ES Год назад

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

@GoodlyChandeep Год назад

Glad you like it!

@obaidulbau 11 месяцев назад

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

@GoodlyChandeep 11 месяцев назад

Thanks, will do!

@robertlevak 3 месяца назад

YEAH! This is cool. Thank you so much!

@EricaDyson 10 месяцев назад

As you said, that is AWESOME!

@izzatkiswani 9 месяцев назад

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

@prasadraja17 7 месяцев назад

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

@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 _} }

@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!

@ronnieyandell931 3 месяца назад

An excellent video. Thanks

@ricardobunge5957 Год назад

Always fantastic stuff, Chandeep! Thanks again.

@GoodlyChandeep Год назад

Glad to hear that!

@AAAExcel Год назад

Last trick is really amazing - thank you 🙏

@GoodlyChandeep Год назад

You're welcome!

@ivancuadros8731 Год назад

Really good tips, saved the best for last!

@GoodlyChandeep Год назад

Thanks!

@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) )?

@KuldeepSingh-nq1vi Год назад

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

@GoodlyChandeep Год назад

Thank You!

@excel-in-g Год назад

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

@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," "," ") ) )

@joaquimcosta952 6 месяцев назад

Very good solution....

@syedaneesdurez9880 Год назад

Thank you very much for the amazing solution.

@GoodlyChandeep Год назад

Always welcome

@AnbarasuAnnamalai Год назад

Much useful in cleaning the data, Thank you very much

@GoodlyChandeep Год назад

Glad to hear that

@hoaithuongluu2869 11 месяцев назад

That's awesome ❤❤❤ thanks so much bro

@martyc5674 Год назад

Excellent as usual Chandeep 👍 👍

@GoodlyChandeep Год назад

Thanks a ton

@ThGrum 3 месяца назад

I'm addicted to your videos.😋

@GoodlyChandeep 3 месяца назад

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

@SeyPras. 4 месяца назад

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

@Ypurenete 9 месяцев назад

Espetacular!!

@davidfelipepenaguardiola1177 Год назад

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

@GoodlyChandeep Год назад

Glad you like it!

@advrohitowhal9794 Год назад

Excellent Boss!! Thanks Bro!!!

@GoodlyChandeep Год назад

You're welcome!

@randolfojolongutierrez5311 Год назад

Belleza!!!

@GoodlyChandeep Год назад

Thanks!

@karimallahwala7022 Год назад

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

@GoodlyChandeep Год назад

Glad you like it !

@incoref 7 месяцев назад

Thank you!

@txreal2 Год назад

That is awesome!

@navisalomi Год назад

Awesome. Your contents are wonderful.

@GoodlyChandeep Год назад

Thanks!

@muhnuur Год назад

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

@GoodlyChandeep Год назад

Thanks !

@z.719 Год назад

Very useful function! Thank you Sir.

@GoodlyChandeep Год назад

Glad you like it

@s961201 3 месяца назад

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

@ratiram9556 Год назад

Great very useful trick . Thanks.

@GoodlyChandeep Год назад

Glad you liked it!

@mukeshbehera4499 5 месяцев назад

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.

@agustinrajan8621 Год назад

God level 🙏🙏.. Really loved it.

@GoodlyChandeep Год назад

Thank you !

@Luciano_mp Год назад

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

@GoodlyChandeep Год назад

Glad you enjoyed it!

@vamshimedishetty5624 28 дней назад

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.

@rohit2492 Год назад

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

@santoshpv321 Год назад

This is godly! Super o super....

@GoodlyChandeep Год назад

Thank you!

@ranjitramakrishnan6823 Год назад

Hi Chandeep, you are amazing👍

@GoodlyChandeep Год назад

Hey, thanks!

@tnssajivasudevan1601 Год назад

Thank 😊, very interesting

@GoodlyChandeep Год назад

Glad you liked it!

@julie_chen Год назад

👏 Awesome

@GoodlyChandeep Год назад

Thank you!

@ExcelWithChris Год назад

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

@GoodlyChandeep Год назад

Glad you enjoyed it!

@abhijeetshetye3764 Год назад

Amazing 👌🏻

@GoodlyChandeep Год назад

Thank you!

@surajchavan6447 Год назад

Awesome 😎😎👍🏻

@GoodlyChandeep Год назад

Thanks ✌️

@equilibriocontabilidadeadm5277 Год назад

Great Video

@GoodlyChandeep Год назад

Glad you enjoyed it

@rajendrakumaroram9812 Год назад

Amazing👍👏👏👏

@GoodlyChandeep Год назад

Thanks!

@faisalag9611 Год назад

Awesome.

@GoodlyChandeep Год назад

Thanks!

@modernboutique1389 Год назад

Another great learning

@GoodlyChandeep Год назад

Glad it was helpful!

@modernboutique1389 Год назад

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

@ShubhamSharma-ls6hj Год назад

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

@telclissonperes Год назад

Bruxaria! 🙂

@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.

@FRANKWHITE1996 Год назад

Thanks for sharing ❤

@GoodlyChandeep Год назад

Glad you enjoyed it!

@FRANKWHITE1996 Год назад

@@GoodlyChandeep always🙌

@DhruvDua88 Год назад

Brilliant

@GoodlyChandeep Год назад

Thanks!

@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 3 месяца назад

You can try Text.Insert function

@sebastiannorge2980 Год назад

Thanks

@GoodlyChandeep Год назад

Glad you like it!

@balajikomma541 Год назад

kindly make video on Evaluate DAX function

@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 💚

@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.

@alphabarry3448 8 месяцев назад

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 6 месяцев назад

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

@brij26579 Год назад

👌👌

@GoodlyChandeep Год назад

Thanks!

@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 6 месяцев назад

Text.Trim function removes leading and trailing spaces.

@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 5 дней назад

How to deal with null in columns

@PawelNap Год назад

👍

@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???

@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?

@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

@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 6 месяцев назад

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

@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

@mohamedmubeen2913 Год назад

Failed to save modifications to the server. Error returned: 'The following system error occurred: Class not registered '. This shows while loading data what can I do?

@ahmednageb8459 Год назад

How to dynamic date start jan 2022 end Des 2022 by pòwer query

@Rice0987 Год назад

I know the best way to remove many spaces between words - just cut fingers they make. 😂👌 If seriously i really don't understand when somebody always uses double or even more spaces for format their string. May be it is good for our eyes for clear see each word separately, but that data is also for PQ and it is always a butthurt and wating time to clear it from dirt to get right result!

@Rice0987 Год назад

I hope that regex can overcome this enternal problem. 🤔