Cleaning Messy Data | Power Query Case Study

This video is dedicated to a user's question on Data Cleansing, which I thought was nearly impossible but Power Query makes it super easy to clean messy data sets. There are ton of learnings you can draw from this data cleansing case study. Enjoy!
===== 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 with a Project 💻- www.goodly.co.in/consulting/
Download File ⬇️- www.goodly.co.in/wp-content/u...
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - [e-mail]
===== CHAPTERS =====
0:00 Intro
1:08 The Data Cleaning Problem
4:20 Power Query Clean Up
12:48 My Online Courses
===== 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!
- - - - -
Music By: "After The Fall"
Track Name: "Tears Of Gaia"
Published by: Chill Out Records
- Source: goo.gl/fh3rEJ​
Official After The Fall KZbin Channel Below
kzbin.info/door/GQE...
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)
Full license here: creativecommons.org/licenses

[@JoseMariaGomezMartinez]

Hi Chandeep! Thanks again gentleman! Superb solution

[@GoodlyChandeep]

Thank you! This is your video :D

[@alphamaniac9411]

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!

[@pondyanand]

Excellent solution ! Really helpful.

[@CraigDG]

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.

[@Belakavadi]

Awesome mate. Love the approach.

[@martyc5674]

Brilliant Chandeep, great practical content.

[@somanathking4694]

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

[@kennethstephani692]

Great video!

[@cherianiype]

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

[@GoodlyChandeep]

Glad you liked it!

[@mathewinmuscat]

Super!!!!! Thanks a ton

[@krishnakishorepeddisetti4387]

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

[@GoodlyChandeep]

Thanks a ton

[@viveksharma4193]

Truly awesome

[@leosaghathan2895]

Yes definitely helpful👌👍🏼

[@BiggBrro]

Magnificent!

[@saniyanulkar9093]

Really amazing solution 👍

[@MdShahidulIslamshafimbd]

Great solution 👌

[@subbu_ca]

That was awesome.

[@boissierepascal5755]

Clever, brilliant !

[@GoodlyChandeep]

Thanks!

[@luisjavier1284]

Hi, Chandeep that was awesome!

[@decentmendreams]

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.

[@judyrodbryanvicente8638]

More power to your channel.. Good stuff mate

[@GoodlyChandeep]

Thank you!

[@user-tp3jq8qg8l]

Really excellent sir.

[@jaychaudhary1284]

great case study

[@RobertJohnstonrobjomabri]

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

[@querobinenator]

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

[@GoodlyChandeep]

Thanks :)

[@GosCee]

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

[@GoodlyChandeep]

Glad it was helpful!

[@lucianoriquet8552]

Amazing!

[@GoodlyChandeep]

Glad you like this Luciano!

[@wayneedmondson1065]

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

[@GoodlyChandeep]

Thank you! Cheers!

[@Laxmanmane007]

superb Video😊

[@cblondhe]

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

[@GoodlyChandeep]

Glad you liked it

[@tanveerabbas3271]

GREAT BOSS

[@neerajnirantar]

Great teaching technique.

[@GoodlyChandeep]

Glad you think so!

[@KhalilAhmad74036]

Great, information, Sir. Thanks,

[@GoodlyChandeep]

Glad you liked this Raza!

[@pabeader1941]

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.

[@ravimalik1264]

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

[@ahmedbenchaoued9765]

Perfect bro keep going , you are good 🤙

[@GoodlyChandeep]

Glad you like it 😊

[@dikushnukenjeh9072]

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

[@vashisht1]

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

[@GoodlyChandeep]

Glad you liked it

[@abdulrehman56]

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

[@ArmanAper7]

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

[@yousrymaarouf2931]

You are great

[@excel-k-sir]

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.

[@maheshmulik2399]

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

[@frankschadler9407]

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

[@pratyushnigam8956]

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

[@fahadea1]

Awesome bruh 😮😮

[@GoodlyChandeep]

Thanks!

[@adityasharma0101]

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)

[@kundanbhardwaz6363]

How we can connect aditya

[@bharathramc.n7796]

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.

[@purepenmicheal140]

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.

[@Nethra7]

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

[@mohitupadhayay1439]

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.

[@RandomlyWisdom]

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

[@GoodlyChandeep]

It never works in the first go. I understand, I've been in your position several times :)

[@RandomlyWisdom]

@@GoodlyChandeep I think we skipped weekend dates. Is there any way to include complete calendar dates?

[@WernervanWyk2]

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 👏

[@GoodlyChandeep]

Going back to the problem via power query would be quite a task. However Power BI visuals might help reformat the data back into its original form.

[@kastenivkimbo5347]

How to get this data sheet dataset to practice

[@eslamfahmy87]

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

[@gravestoner2488]

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"

[@nettenette2298]

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

[@GoodlyChandeep]

Send me a sample and expected output. If it seems a common problem, I'll make a video on it :) [e-mail]

[@apoorva528]

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

[@003kashif]

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

[@amahcynthia7405]

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.

[@GoodlyChandeep]

Can't say unless I see the screenshot or your query :|

[@amahcynthia7405]

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

[@kartickchakraborty9135]

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.

[@GoodlyChandeep]

Granularity means one row of any table!

[@kartickchakraborty9135]

@@GoodlyChandeep Would you kindly make videos on this topic?

[@abeerattia4523]

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

[@GoodlyChandeep]

Value.Type([Column2])

[@txreal2]

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

[@GoodlyChandeep]

I'll have to do a video on this. Thanks for the suggestion!

[@sanjeevkhakre2990]

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

[@GoodlyChandeep]

Delete that step

[@stephenbui490]

1 Step I've put my leg on ;P

[@lohitgowda5889]

where do I find the file for practice ?

[@GoodlyChandeep]

www.goodly.co.in/wp-content/uploads/2022/02/Data.zip

[@sauravsinha6939]

Is there any course on M language that I can study

[@GoodlyChandeep]

Working on creating a course!

[@deepaksahu-nf1vb]

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

[@GoodlyChandeep]

Hi Deepak. I understand. Please wait for Black Friday offer and enjoy KZbin videos until then :)

[@preethiagarwal5355]

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

[@powerbinareal]

Insano!!! #powerbinareal

[@GoodlyChandeep]

Thanks !

[@deshn21]

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

[@goldylock]

Ive no idea about all his code there, no live explanation

[@robertowerneck6902]

Great video!