ฝัง
- เผยแพร่เมื่อ 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 - th-cam.com/video/RIo4OrPixco/w-d-xo.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!
My man, it's simply amazing. You're the best. Thanks a lot.
List.Reverse([Position]) to insert delimiters from the last position instead, will eliminate need of shift calculation
Didn't think of that.. Thanks 😁
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.
I can't thank you enough. You're brilliant!
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
Thanks for your Explanation. Try it right away on Monday 😊
Thank you for your insights! I really enjoyed this presentation and learned some techniques!
Super detailed tutorial. Thanks
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.
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.
Excellent. Looking forward to more videos like this with advanced level ❤👍
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 😅
very very excellent, Thanks for your Great explanation
Thanks for sharing this
Thanks!
Amazing...Advanced M.....Fantastic
Missed the Live session but definitely watching the recording!
Hello from Zimbabwe, missed the live session, but practicing with the recorded video. Thank tutor
Thank you
Wonderful
❤❤❤❤ super bro thanks 👍👍👍
It's amazing!!!!!!!!!
You are one of the best in this language Goodly. 🎉🎉 Congrats. NASA level
Glad you think so Willian!
Joining from Spokane, WA - United States
Thanks, well i get the same results by using "Column from Example" solely by using UI in 10 steps
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.
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!
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 :)
You are a legend.
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]," ")}})" .
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 ;)
Hello, Chandeep, Deepak joining from Poland
both!!
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
40:16 Could we have removed the dashes before splitting the columns?
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?
Cartago, Costa Rica
finished watching
You are awesome 👍
Thanks Amit!
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 really a STAR .. Thanks for sharing your knowledge ! Love you Bro !
ya chandeep i learnt m code... but still practice more
App acha teach kertay han please hindi ma be explain kiya kren
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.
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
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)
M from Indore bro
Why not use space to separate columns, which saving some steps!
This example is arranged with fixed width. What if each field is with variable width?
I use power query instead of M-code, but I am interested in learning it if it will save time. CT
💯👍
36:00 what about using text.replace and replace spaces with pipe ( | ) character
Delhi ncr
I am Prabhakar from Bangalore
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
How to mail you??
🙂
From Madagascar
USA NJ I use the user interface
Hi how are you
Pune
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
Interface
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.
Coimbatore
Hey
Can anyone help me to clean data & visualize in power bi
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.
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
Geneva
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…🤔…
Bhavnagar gujarat
Familiarising or updating excel knowledge
41:33 Nor do you know how many junk characters there will be.
please provide original file not the output file.
finished watching