🔴 Solving Advanced Data Cleaning Problems in Power Query (+ downloadable files)
Вставка
- Опубліковано 5 тра 2023
- Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
Download Output - www.goodly.co.in/advanced-pow...
===== 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
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - goodly.wordpress@gmail.com
===== 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! - Наука та технологія
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 - ua-cam.com/video/RIo4OrPixco/v-deo.html
Hi I have found that the links to your courses and workbooks don't work, Is there an issue with your site ?
@@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 - goodly.wordpress@gmail.com
I can let you know when we are up and running
@@GoodlyChandeep thanks will drop you a message I have become quite a fan of your content
@@willm7994 thanks, The website is working now.
@@GoodlyChandeep thanks just downloaded a workbook 👍
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
Seriously impressive!
List.Reverse([Position]) to insert delimiters from the last position instead, will eliminate need of shift calculation
Didn't think of that.. Thanks 😁
My man, it's simply amazing. You're the best. Thanks a lot.
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.
Thank you for your insights! I really enjoyed this presentation and learned some techniques!
Thanks for your Explanation. Try it right away on Monday 😊
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.
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
Super detailed tutorial. Thanks
I can't thank you enough. You're brilliant!
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.
Excellent. Looking forward to more videos like this with advanced level ❤👍
very very excellent, Thanks for your Great explanation
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 😅
You are one of the best in this language Goodly. 🎉🎉 Congrats. NASA level
Glad you think so Willian!
Missed the Live session but definitely watching the recording!
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]," ")}})" .
Thanks for sharing this
Thanks, well i get the same results by using "Column from Example" solely by using UI in 10 steps
Hello from Zimbabwe, missed the live session, but practicing with the recorded video. Thank tutor
Amazing...Advanced M.....Fantastic
Thanks!
App acha teach kertay han please hindi ma be explain kiya kren
❤❤❤❤ super bro thanks 👍👍👍
Joining from Spokane, WA - United States
Thank you
ya chandeep i learnt m code... but still practice more
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.
Thanks :)
Perfect Session. Please do a live session at least once a month. Thanks
Glad you like this Anurag! Yes, we are doing one live session a month!
Wonderful
you are really a STAR .. Thanks for sharing your knowledge ! Love you Bro !
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 🎉🎉🎉
Thank you so much ;)
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.
Hello, Chandeep, Deepak joining from Poland
At 35:00 isnt it better to sort list descending order so that symbols insert from positions at end of string?
Seattle, WA ... Mostly DAX editor
It's amazing!!!!!!!!!
finished watching
Cartago, Costa Rica
You are a legend.
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?
40:16 Could we have removed the dashes before splitting the columns?
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.
You are awesome 👍
Thanks Amit!
This example is arranged with fixed width. What if each field is with variable width?
both!!
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?
pinned comment
@@GoodlyChandeep Thank you for the file, very good video I learned a lot about the possibilities of List.Accumulate
I use power query instead of M-code, but I am interested in learning it if it will save time. CT
I am Prabhakar from Bangalore
M from Indore bro
You could also have used function Splitter.SplitTextByPositions
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.
@@kebincui unfortunately I‘m not using Power BI any more (retired)
Why not use space to separate columns, which saving some steps!
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.
36:00 what about using text.replace and replace spaces with pipe ( | ) character
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…🤔…
USA NJ I use the user interface
Hi how are you
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
From Madagascar
Hi Chandeep, Pleaseeeee make video on how to upload zip nse bhavcopy to power query from web
@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.
Delhi ncr
Sir in power Bi how we can keep nth length space Between two text ....? And that space should be visible in visualisation as well
Text.Pad function in Power Query might help
Familiarising or updating excel knowledge
Geneva
Bhavnagar gujarat
India
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
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?
I really don't know the answer to this 😐
@@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.
💯👍
🙂
How to mail you??
Coimbatore
Interface
Pune
Hey
Can anyone help me to clean data & visualize in power bi
please provide original file not the output file.
41:33 Nor do you know how many junk characters there will be.
finished watching