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 - kzread.info/dash/bejne/hH2jlrGrgMzek9I.html

@willm7994

9 ай бұрын

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

@GoodlyChandeep

9 ай бұрын

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

@willm7994

9 ай бұрын

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

@GoodlyChandeep

9 ай бұрын

@@willm7994 thanks, The website is working now.

@willm7994

9 ай бұрын

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

@alexhsieh8348

Жыл бұрын

Seriously impressive!

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

@GoodlyChandeep

Жыл бұрын

Didn't think of that.. Thanks 😁

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

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

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

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

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

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.

Super detailed tutorial. Thanks

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

very very excellent, Thanks for your Great explanation

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.

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

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 😅

Missed the Live session but definitely watching the recording!

Thanks for sharing this

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.

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

❤❤❤❤ super bro thanks 👍👍👍

Thank you

Wonderful

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

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

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

@GoodlyChandeep

Жыл бұрын

Glad you think so Willian!

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

Joining from Spokane, WA - United States

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 :)

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.

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

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!

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 ;)

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]," ")}})" .

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

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.

Hello, Chandeep, Deepak joining from Poland

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?

both!!

Seattle, WA ... Mostly DAX editor

finished watching

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

Cartago, Costa Rica

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

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 👍

@GoodlyChandeep

Жыл бұрын

Thanks Amit!

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

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

You could also have used function Splitter.SplitTextByPositions

@kebincui

2 ай бұрын

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

2 ай бұрын

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

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

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

💯👍

M from Indore bro

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

7 ай бұрын

I really don't know the answer to this 😐

@yesmayhem

7 ай бұрын

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

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

I am Prabhakar from Bangalore

Delhi ncr

🙂

USA NJ I use the user interface

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

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…🤔…

From Madagascar

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

4 ай бұрын

Text.Pad function in Power Query might help

Hi how are you

Interface

Geneva

Pune

Coimbatore

How to mail you??

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

@IrshadKhan-vk8rn

6 ай бұрын

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

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

Familiarising or updating excel knowledge

Bhavnagar gujarat

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

please provide original file not the output file.

finished watching