Cleaning Messy Data | Power Query Case Study 

Hi Chandeep! Thanks again gentleman! Superb solution

Great example. It forces someone to logically think about what to do, and allow PQ to do the rest. It's a powerful tool. Thanks for your insight and great explanation!

Chandeep... You are awesome man.... Your content is great... I work in power bi day in and day out... But after seeing your videos... I feel... There is still a lot to learn

Thankyou Goodly, your info helped a little. I have a very messy daily data dump to clean up every day coming from external software within the company. The cleanup of the data becomes crucial in enabling an accounting reconciliation of the data, and until the data is clean, the manual reconciliation process is long and arduous and takes me away from my other work. I have found no one yet on You Tube, who has the exact problem I have and provides an exact solution, but bit by bit I am putting pieces together from different people like you that will hopefully give me the right solution i need.

I too came across the exact hurdle and used similar techniques in PQ. Great work Chandeep

Thanks, brother I am planning a project management solution, this will help the resourcing part of it and bring the necessary data through the the pivot tables.

Hi Chandeep @ you are always Power Query Data cleaning Super Hero

Thanks. I would have used if column A = null then give me column B else null to get the months . But happy to learn this technique of isolating cells by data type . Also, I love your technique of getting the date/month/year . I have projects that I would like to visit after watching this vid.

This is not data cleaning; this is data rearranging. Cool applications.

This is literally superb sir..I was like mesmerised no words. Thankyou sir .

Thanks for the video, helped me a lot. Just one question wouldn't it be better to add one more column called present/absent and shift the "H" value there and replace "H" with 0 in the attendance column, so the whole column becomes numeric and we can perform numeric operation like sum on it ??

I paused the video at the start and tried on my own. The solution was not easy. Thanks for new data cleaning tricks.

Thank you very much Chandeep, I have to solve this problem every year to get family rosters into a Google calendar - I usually spent time getting the data manually in a row then do the transformations 🤦‍♂️. I will try this for next year

I like how you used that Value.Type function to isolate the date. I had a similar problem to solve and used the fact that US dates have a / in them. I like yours better as it would be more generic.

Awesome Awesome Awesome man.. You are rock star ... Dear please make a video that how you learn and journey of Power Query. I have asked you in one of previous video....

We talk about Dax so much but forget that Power Query is so much effective at doing lot of things too. Much better than python or other languages to cleanse data and transformation.

Hi, Chandeep that was awesome!

Great teaching technique.

Really excellent sir.

I am doing the similar power query data exploitation but in different ways. I found yours to be more dynamic. Like, when you needed to add a custom column containing only the months, you applied the logic that, if, column 2 date type is month, then bring column 2 else null. I used to apply the following logic, If column one is null, then bring column 2 else null. Also, I would have done the specific date extraction differently because in your logic, I was relying on column number logic (-1). I would have kept the row containing “team” in column 1 and Would have separated out the date and unpivoted. Thereby using the file date instead of my own logic. The date combining logic was very cool though(bringing day from one column and month and year from another)

great case study

Chandeep , this was another amazing video, thanks for sharing.

Thank you for the nice video . It was really helpful. Request please make some videos related to 1mmt,3mmt,6mmt,YTD and calendar year and comparing them with previous year or prior period. Also, some tips and tricks using time intelligence dax

Man... when my boss says "make a report, heres my data, and every time I change it, i want it to update" and I say "sure thing boss, can you organize it in this format?" And he says "no, I prefer pretty colors and 4 tables with 3 sub tables each all on the same sheet, make it work" I can now say "sure thing boss man"

Clever, brilliant !

Excellent solution ! Really helpful.

Super terrific Chandeep! Incredible stuff man! Thank you for this!

Brilliant Chandeep, great practical content.

Yes definitely helpful👌👍🏼

Thanks for that, Chandeep. Impressive! This will undoubtedly help me in my quest to master power query.

Hey Chandeep, It was really a well presented solution. Right now I have been struggling to clean the Mutual funds Consolidated Account Statement that comes in PDF but with no success using power query. Just wanted to know can that also be cleaned and get exported to excel. If you want to have a look at the data please confirm will share the same.

Truly awesome

Great video!

Hi Chandeep. Awesome solution! Well explained and very instructive. Thanks for demonstrating. Thumbs up!!

Great, information, Sir. Thanks,

Fantastic sir... it's just amazing would you please suggest any dataset examples to hone my data cleaning skills on power query.........if you could please help me sir.....

Awesome mate. Love the approach.

Actually, amazing 👏 but I am facing an issue with knowing which function it needs to use on most of PQ data ... I know ( that's why you should take my course !... but I trying to do my best, but 😢 the result is above 50%..so could you provide with me the tips & tricks to be follow

Perfect bro keep going , you are good 🤙

Hi Chandeep!, thanks for your videos. Do you have any advice for my case where I have an excel file downloaded from an accounting system and this file is nothing but a banch of grouped rows and number of levels (subgroups) vary all in one column. The report which is the goal is supposed to be dynamic. Is there a way to transform grouped excel file into flat file? Thanks in advance

Great solution 👌

Really amazing solution 👍

Smart and clean solution, if the dates are always complete. Excel can be used to be 'creative'with data in one or another way. ;-)

Thanks for explaining the custom column usage of converting the number to the required date. Please do explain how to use PQ when the raw data is 40,000+ rows and is cleaned and converted to the required format, when we go to the query for necessary correction PQ takes time or msg is displayed something has gone wrong.

Lekker Chandeep! Your biggest fan in Africa🌍 Do you have a solution wherby one can take change entered data BACK TO the the original complex date template shown above? Keep up the good work 👏

superb Video😊

More power to your channel.. Good stuff mate

GREAT BOSS

Thanks chandeep for this video please I have two issues firstly, what if the data type of the date it date/time? With this your formula for extracting date brings error. How can I adjusted it? I tried changing the data type of column 2 to date but it destroyed other datas in that column to errors. Secondly can you help on my power bi date changing from 2022 to 1899 automatically.

Amazing!

👌 awesome.. I like these challenge video...also the logic to the problem was great 👍

Thank you sir, i have a doubt in this video that in this example after unpivote, the null values are missing i mean that the null should get zero am not getting can u please help me...

Super!!!!! Thanks a ton

That was awesome.

Magnificent!

Do you have any videos on organizing messy payroll data using power query?

Hi Sir, how are you? I recently finished your power query tutorials. Now, I'm learning Dax from basic. But, Recently I came across a term "Granularity" while learning Dax from ExcelIsFun. I am unable to understand why did he enforce on this term repeatedly? Please kindly make a video on this topic.

Hi Chandeep, Could you provide us the excel sheet of the dirty data?

Awesome bruh 😮😮

I have a messy data question. How do you remove the line feeds or carriage returns in column headers in Power Query? Please help.

How to get this data sheet dataset to practice

You are great

Can you provide us the file to practice alongside the video?

Hi Chandeep , when i upload the fill to power query and tried to extract the date i got this Erro (Expression.Error: We cannot apply field access to the type Function. Details: Value=[Function] Key=Column3 If Value.Type [Column2] = Date.Type then [Column] else null Pls. advise

Hi Goodly, thank you so much for this. I have been able to replicate it. However, I wanted to confirm, so at the end of the steps I tried to convert the date type to UK date structure using locale, however this didn't work. Then I tried slitting the individual dates, rearranging and merging them back, but when I converted the data type to date it showed error. Is there a reason why.

Sir when I load the data in power query. Then the date converting into any data type which creating prblm to apply the function. Please help

H helpful

Is there any course on M language that I can study

Hi Goodly, this is awesome. Please I will like to ask is step three (removing other columns) necessary, cos I can only see 32 columns in mine. Are you assuming that the other columns are invisible and you don't want it to disrupt your clean up. In summary what is the purpose of step 3. I will appreciate your response. Also how did you remove "other columns". Did you manually type the formular or .........

where do I find the file for practice ?

We want to join your course but it little high there is no one who teach like you with experience you possess but only cost is taking us back

1 Step I've put my leg on ;P

I dint understand the problem itself , bro. 2 nd column became first of the month 🙄

Insano!!! #powerbinareal

Instead of going through the steps you already made. Go through the steps in real time. This is extremely lazy work.

Great video!