🔴 Solving Advanced Data Cleaning Problems in Power Query (+ downloadable files)

Check out our newly launched M Language course ↗️ - goodly.co.in/l...
Download Output - www.goodly.co....
===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -
goodly.co.in/l...
✔️ Power Query Course-
goodly.co.in/l...
✔️ Master Excel Step by Step-
goodly.co.in/l...
✔️ Business Intelligence Dashboards-
goodly.co.in/l...
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co....
Corporate Training 👨‍🏫 - www.goodly.co....
Need my help on a Project 💻- www.goodly.co....
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - [e-mail]
===== 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!

[@GoodlyChandeep]

Download the output file here - goodly.co.in/wp-content/uploads/2023/05/Solving-Tricky-Data-Cleaning-Problems-Ouput.xlsx and please watch this video on List.Accumulate - kzbin.info/www/bejne/iHrSZYKohc6rmdE

[@willm7994]

Hi I have found that the links to your courses and workbooks don't work, Is there an issue with your site ?

[@GoodlyChandeep]

@@willm7994 Yes our hosting is down for a bit. It should be working anytime soon. Thanks for being patient. If you can drop me an email - [e-mail] I can let you know when we are up and running

[@willm7994]

@@GoodlyChandeep thanks will drop you a message I have become quite a fan of your content

[@GoodlyChandeep]

@@willm7994 thanks, The website is working now.

[@willm7994]

@@GoodlyChandeep thanks just downloaded a workbook 👍

[@konstantinevseyenko9312]

List.Reverse([Position]) to insert delimiters from the last position instead, will eliminate need of shift calculation

[@GoodlyChandeep]

Didn't think of that.. Thanks 😁

[@Excelambda]

Super cool!! A formula alternative: =LET(a,B4:B22,b,C4:C22,u,UNIQUE(a), w,BYROW(u,LAMBDA(x,LET(t,TEXTSPLIT(TEXTJOIN(" ",,IF(x=a,b,"")),{"-"," ","="},,1),TEXTJOIN(" ",,--FILTER(t,ISNUMBER(--t)))))), HSTACK(u,IFERROR(--TEXTSPLIT(TEXTJOIN(",",,w)," ",",",,,""),""))) Note: The results are numbers, not text

[@alexhsieh8348]

Seriously impressive!

[@RaviGupta-mo1nf]

The efforts you put in to bring such wonderful videos shows the passion you have. I have been learning a lot and also becoming more passionate about PQ, PBI & Excel. To me, you are a true Guru and I cannot thank you enough. I repay your debt by teaching/helping others without payment. Thank you once again Chandeep.

[@rhaps2008]

Thank you, I always end up with a headache trying to work out how best to fix messy data from excel and pdf files, your explanation on List.Accumulate and how to get the results I want helps me so much

[@gustavobarbosa906]

It is amazing how you can combine several complicated transformations into a few steps. The main lesson I took from all this is what you said in the beginning: if one wants to do anything more advanced one should preferably use lists. Thank you for this very interesting video.

[@tak0331]

Great explanation as always! Best part is how you calmly troubleshot errors on the fly - this helped the training resonate even more. Let’s get this man 100k subscribers asap.

[@raimundojs9547]

I can't thank you enough. You're brilliant!

[@willianthalles]

You are one of the best in this language Goodly. 🎉🎉 Congrats. NASA level

[@GoodlyChandeep]

Glad you think so Willian!

[@kebincui]

Excellent. Looking forward to more videos like this with advanced level ❤👍

[@orleanCosta]

My man, it's simply amazing. You're the best. Thanks a lot.

[@profcor1820]

Thanks for your Explanation. Try it right away on Monday 😊

[@JJ_TheGreat]

Thank you for your insights! I really enjoyed this presentation and learned some techniques!

[@anuragkumar-dm1er]

Perfect Session. Please do a live session at least once a month. Thanks

[@GoodlyChandeep]

Glad you like this Anurag! Yes, we are doing one live session a month!

[@DannyChan-o8l]

This is a very superb transformation. The crux is making use of the implicit looping in M functions to do the transformations. This example appears complex because it involves two level of looping and application of multi-level looping using M-code transformation is not intuitive to the general folks. Anyway, thanks Chandeep for your detailed explanation with this example.

[@GoodlyChandeep]

Thanks :)

[@stefaanlouette]

My man, you are super, clearly explained. Key take away for me is that I need to step up my game when I see you do this M magic 😅

[@seamushand8439]

Super detailed tutorial. Thanks

[@AmirHosseinGolshani]

very very excellent, Thanks for your Great explanation

[@kasondekasondel2885]

Hello from Zimbabwe, missed the live session, but practicing with the recorded video. Thank tutor

[@cristian.angyal]

Missed the Live session but definitely watching the recording!

[@dwijenbaruah8257]

This is great !! What about Split Column and applying By Non-digit To Digit after query " Table.Group(#"Changed Type", {"Transaction NO"}, {{"All", each Text.Combine(_[Payment]," ")}})" .

[@hoaithuongluu2869]

So WoW, wish you the best and hope to see more video from you. Thanks to you, I always have "surprised" moment whenever watching your video 🎉🎉🎉

[@GoodlyChandeep]

Thank you so much ;)

[@007lbp]

Amazing...Advanced M.....Fantastic

[@midmindconsulting1298]

you are really a STAR .. Thanks for sharing your knowledge ! Love you Bro !

[@josephindahl548]

Joining from Spokane, WA - United States

[@kimsanov]

At 35:00 isnt it better to sort list descending order so that symbols insert from positions at end of string?

[@dirkstaszak4838]

Brilliant ❤

[@JJ_TheGreat]

16:37 Hey Chandeep, Question: You had named your query here “Cols”. So why is it that after you converted it to a list, the M code is referencing “Cols1”? Thanks.

[@chrisseelbach3746]

Thanks!

[@JJ_TheGreat]

46:35 Rather than trying to guess the delimiters, is there a way you can write in the code something like Is.SpecialChar() - kind of like Is.Num() - and use something like that to clean it up?

[@nazarkamal8831]

❤❤❤❤ super bro thanks 👍👍👍

[@yashrsingh]

Thanks for sharing this

[@hokki28]

You could also have used function Splitter.SplitTextByPositions

[@kebincui]

This seems a good idea, but it is extremely dificult to implement. I tried but failed. The key obstacle is that Table.SplitColumn() with Splitter.SplitTextByPositions cannot get the positon values from the other column, it only handle the data in each own column respectively. If you can do thsi, please kindly share your code. Thanks in advance.

[@hokki28]

@@kebincui unfortunately I‘m not using Power BI any more (retired)

[@DeepakKashyap-fr3ek]

Hello, Chandeep, Deepak joining from Poland

[@giovanni_a67]

It will never be too early for Microsoft to start implementing some regular expressions functionality in their mainstream software. If MS read this, they will retaliate removing that little regexp-like stuff that they have in Word :-) This case would have been an ideal candidate for an easy solution. Thank you for the videos, there is always some interesting tip and technique to be found in them.

[@shivanandbiradar1277]

Thank you

[@carolshipley7903]

Wonderful

[@SandipanSarkar-c8v]

finished watching

[@khurramtoufeeq8417]

App acha teach kertay han please hindi ma be explain kiya kren

[@craigchang699]

This example is arranged with fixed width. What if each field is with variable width?

[@PRABHAKARREDDYBASIREDDY]

ya chandeep i learnt m code... but still practice more

[@pakipower]

Thanks, well i get the same results by using "Column from Example" solely by using UI in 10 steps

[@alanfeucht9503]

Seattle, WA ... Mostly DAX editor

[@jackjobriencorso]

I use power query instead of M-code, but I am interested in learning it if it will save time. CT

[@JJ_TheGreat]

40:16 Could we have removed the dashes before splitting the columns?

[@Travel-Costa-Rica]

Cartago, Costa Rica

[@kadirkkkk]

36:00 what about using text.replace and replace spaces with pipe ( | ) character

[@mohamedosama6409]

You are a legend.

[@arungupta8444]

What if the Source File is used for the Merge + Append + Group transformations with other files . but now the source file got Updated with Lots of Data and it is difficult to update the file and we need to replace the source file alltogether, with new file with same name, without affecting the previous output. Can it be done with "Parameters" Setting or something else. Please Advise.

[@jiaoyang6780]

Why not use space to separate columns, which saving some steps!

[@antonysovy5887]

It's amazing!!!!!!!!!

[@kimboard6720]

both!!

[@SouravMondal-hb8rz]

Sir in power Bi how we can keep nth length space Between two text ....? And that space should be visible in visualisation as well

[@GoodlyChandeep]

Text.Pad function in Power Query might help

[@danielblanc1370]

Thank you for this live and your clear explanations. I searched for the files to download but couldn't find them, could you attach them please?

[@GoodlyChandeep]

pinned comment

[@danielblanc1370]

@@GoodlyChandeep Thank you for the file, very good video I learned a lot about the possibilities of List.Accumulate

[@dharmendrasharma5449]

M from Indore bro

[@MamtaChawta]

can u make a video on power query template...how to make a template n reuse it..everymonth my complete data is deleted n new report is pasted..how can i use power query

[@PRABHAKARREDDYBASIREDDY]

I am Prabhakar from Bangalore

[@amitk1208]

You are awesome 👍

[@GoodlyChandeep]

Thanks Amit!

[@yesmayhem]

My client has 80s bandwidth. It's going to take a couple of months to get near speed. I notice the with each action processing queries and loads takes longer. The larger the data set the worse it gets. I tried to create the query with a slimmed down data set. same thing. Is there a way to get past horrible bandwidth?

[@GoodlyChandeep]

I really don't know the answer to this 😐

[@yesmayhem]

@@GoodlyChandeep lol Thanks for saying this. There are a couple of issues that Power Query may be missing on. At the point of import. I realized that in explorer you can filter file metadata. You can filter on fields like dates in explorer and in its search bar. Power Query should be able to leverage this and other pre import filtering as it does within its already imported dataset. Doing this would be a nice head start.

[@ddp2049]

USA NJ I use the user interface

[@amitjain7949]

Hi Chandeep, Pleaseeeee make video on how to upload zip nse bhavcopy to power query from web

[@IrshadKhan-vk8rn]

@amitjain7949 brother you use some python functions to automatically download the file to the folder location. Once these file are downloaded then create a function using this link code sql10.blogspot.com/2016/06/reading-zip-files-in-powerquery-m.html?m=1 once you created the function just get the data from the folder once done you can then use this function to extract the files from zip file without actually extracting it to that location. Hope that helps.

[@SSi-nq3rt]

guess that would have been to complicate. 😶‍🌫️ guess i woul have taken the complex way by creating one table per group, cleansed the first table, copy the code to the remaining tables and then append it again into one table. Or….. just do it more or less manually. And then go to the person who created that list AND…🤔…

[@murshidgammeryt5232]

Hi sir ., one quary in power quaries in excel work book 1 sheet have Tractor loan data and 2 sheet have home loan data every month we save can youn solve my quries how that work book both data append in power quries but both should be month wise append 1 sheet Tractor loan data april to dec and 2 sheet home loan data april to dec 2 sheet in power quaries

[@vinayakdaliyan7527]

where this is use in which job profile ??...

[@vinayakdaliyan7527]

cuz it is difficult to remember such type of these things ..

[@shankar49677]

Familiarising or updating excel knowledge

[@satyavansahani1833]

Hey Can anyone help me to clean data & visualize in power bi

[@jonjorombonanilfgard]

From Madagascar

[@khurramtoufeeq8417]

Hi how are you

[@Mr.VJ0527]

Delhi ncr

[@shankar49677]

Coimbatore

[@AbhiGorghate]

Pune

[@Hjmatthebench]

Geneva

[@shankar49677]

India

[@Mr.VJ0527]

Interface

[@rrrraaaacccc80]

💯👍

[@abcabc-kn4fh]

Bhavnagar gujarat

[@honeyishere]

How to mail you??

[@JJ_TheGreat]

41:33 Nor do you know how many junk characters there will be.

[@ashutoshverma1418]

please provide original file not the output file.

[@WendyXie-i2h]

🙂

[@SandipanSarkar-c8v]

finished watching