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.
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
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.
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.
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 ??
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....
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.
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.
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
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
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.....
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)
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.
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...
hi chandeep.. i have an advice for you. category each of ur video as basic, intermidiate and advance on any topic. many times i have left the video finding it too advanced for my work as finance manager.....❤ 8:12
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.
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 👏
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.
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"
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
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 , 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.
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 .........
Hi Chandeep! Thanks again gentleman! Superb solution
Thank you! This is your video :D
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
Thanks a ton
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 paused the video at the start and tried on my own. The solution was not easy. Thanks for new data cleaning tricks.
It never works in the first go. I understand, I've been in your position several times :)
@@GoodlyChandeep I think we skipped weekend dates. Is there any way to include complete calendar dates?
This is not data cleaning; this is data rearranging. Cool applications.
This is literally superb sir..I was like mesmerised no words. Thankyou sir .
Super terrific Chandeep! Incredible stuff man! Thank you for this!
Glad you liked it!
I too came across the exact hurdle and used similar techniques in PQ. Great work Chandeep
Thanks :)
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
Hi Chandeep @ you are always Power Query Data cleaning Super Hero
great case study
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.
Awesome mate. Love the approach.
Excellent solution ! Really helpful.
More power to your channel.. Good stuff mate
Thank you!
Brilliant Chandeep, great practical content.
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 that was awesome!
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 ??
Truly awesome
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....
Great solution 👌
Yes definitely helpful👌👍🏼
Chandeep , this was another amazing video, thanks for sharing.
Glad you liked it
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.
Great teaching technique.
Glad you think so!
Thanks for that, Chandeep. Impressive! This will undoubtedly help me in my quest to master power query.
Glad it was helpful!
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.
Clever, brilliant !
Thanks!
Really amazing solution 👍
Hi Chandeep. Awesome solution! Well explained and very instructive. Thanks for demonstrating. Thumbs up!!
Thank you! Cheers!
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
Really excellent sir.
👌 awesome.. I like these challenge video...also the logic to the problem was great 👍
Glad you liked it
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
Perfect bro keep going , you are good 🤙
Glad you like it 😊
Great, information, Sir. Thanks,
Glad you liked this Raza!
That was awesome.
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.....
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)
How we can connect aditya
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.
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...
hi chandeep.. i have an advice for you. category each of ur video as basic, intermidiate and advance on any topic. many times i have left the video finding it too advanced for my work as finance manager.....❤ 8:12
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.
Super!!!!! Thanks a ton
Great video!
superb Video😊
Smart and clean solution, if the dates are always complete. Excel can be used to be 'creative'with data in one or another way. ;-)
Amazing!
Glad you like this Luciano!
GREAT BOSS
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 👏
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.
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.
Do you have any videos on organizing messy payroll data using power query?
Send me a sample and expected output. If it seems a common problem, I'll make a video on it :)
goodly.wordpress@gmail.com
Awesome bruh 😮😮
Thanks!
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"
How to get this data sheet dataset to practice
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
You are great
Hi Chandeep, Could you provide us the excel sheet of the dirty data?
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.
Granularity means one row of any table!
@@GoodlyChandeep Would you kindly make videos on this topic?
I have a messy data question.
How do you remove the line feeds or carriage returns in column headers in Power Query? Please help.
I'll have to do a video on this. Thanks for the suggestion!
Can you provide us the file to practice alongside the video?
Insano!!! #powerbinareal
Thanks !
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
Value.Type([Column2])
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.
Can't say unless I see the screenshot or your query :|
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
Delete that step
Is there any course on M language that I can study
Working on creating a course!
where do I find the file for practice ?
www.goodly.co.in/wp-content/uploads/2022/02/Data.zip
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 .........
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
Hi Deepak.
I understand. Please wait for Black Friday offer and enjoy TH-cam videos until then :)
H helpful
1 Step I've put my leg on ;P
I dint understand the problem itself , bro. 2 nd column became first of the month 🙄
Magnificent!
Instead of going through the steps you already made. Go through the steps in real time. This is extremely lazy work.
Ive no idea about all his code there, no live explanation
Great video!