Agree with you Chandoo. As data is growing and becoming complex, it will carry some legacy issues and new ones too, so ability to curate and clean it, is going to matter. Thank you for a simple yet powerful tutorial!
Before removing duplicates, it is best to handle the letter cases first. Power Query treats lower case and upper case of the same letter as different characters. I would transform all email addresses to lower case before removing duplicates 😉. Great video, Chandoo!!
With removing duplicates, one could also remove partial payments, in case the administrators entering new students don’t pay attention to duplicates or made typos
Just one Question: What if a student has paid 100 and next time 200, but both these entries are recorded separately so while removing duplicates his 1 entry will be deleted. So how can we counter that?
Chandoo - Your Excel tutorials are well above par and I have learned so much watching your videos. I've been able to impress myself and my colleagues with your tips and tricks. I'm not a data analyst - but data defines my actions and you have introduced me to so many functions and tips that make a HUGE difference in time and pinpointing issues. Very awesome and very well done in every respect. Thank you, you are appreciated.
Hi. Totally agreed. Power Query is amazing. Level of knowledge required to start is minimum, building skills has you go. Power Pivot data model and DAX is also amazing. And ensures extremely complex task can be developed and repeated without fuss. Martin
@@Kirill628 yea vba is better, but it'd have took me 20 minutes of writing a code & debugging it to come to the same result that he did without writing a single line of code.
You can also create a folder that will automatically format itself if you have other raw data, say a different school but need the same analysis so you can drop the file into the power query folder and it will format everything so you don't have to repeat the steps
Awesome lesson Chandoo! Even in retirement, I continue to extensively use Excel & your Power Query videos have opened a whole new realm of possibilities for my Excel needs. Thank you!
If Data is small Power query, Else: SQL is your friend for large DATA sets......thanks Chandoo...Nxt video Pls compare MsExcel=>Power Query==> SQL....Pros & Cons, Industry Requirement.....
I use a lot of Power Query because I make a lot of Power BI dashboards. I spend a lot of time building star schemas - sometimes normalizing, sometimes not - it all depends on what I am trying to do with the data. The point is that I consider it to much more important to model the data for utility than the time I spend on any DAX I might write later. Honestly, its like 80% data modeling and 20% adding visualizations and some measures of calculated tables with DAX later. That said, I recently started going back to pre-formatting more significant transformations in SQL Server rather than trying to clean it up in Power Query. Loading some data into a data mart first to transform it with SQL, and then creating a view I can pull from into Power BI, is often a game changer for not having to do so many transforms in Power BI with Power Query. Put SQL and Power Query altogether and you can provide some very fast loading and amazing data visualization and reports with the Power BI report app too (you know - SSRS 2.0 for the cloud).
@@saurabhdwivedi5866 Creating a good data model, most typically a star schema with a central fact table (or tables) surrounded by dimension tables (the things you will use as filters or slicers) is far more important than what you will do with DAX later. You need to understand the implications of any data model you create because if you don't you will often find yourself going back to the data model and possibly starting from scratch because you have painted yourself into a corner and no amount of DAX will get you out of it. That said, the model you choose will be affected by what existing relationships and cardinality exist within your data as well as your requirements for grain (granularity/precision) and, when very large data sets are involved, balancing those grain requirements against speed requirements. That said, my advice in this respect is, unless you have data that has already been modeled and has a data dictionary (or you are only working a single table or two), then you need to spend some time profiling your data when you start and make an entity relationship diagram (ERD). That ERD will most likely highlight for you what you can and can't do with your data and often leads you to realize what other data you need, but might not have, in order to meet requirements in the ultimate dashboard. With respect to my comment about using SQL first. You will likely often use data from a SQL database using an ODBC connection to pull it into Power BI. A database is typically a lot faster than Power BI and having pre-modeled data coming over from the SQL side will save you a lot of modeling on the Power BI side. Typically what you would do is create a view (or views) on the database side that have your data already as close to perfect as possible so you don't have to spend time cleaning it up in Power BI. Next, you would have a parameterized stored procedure to call against that view, even if the parameter was as broad as * (select everything). Accounts calling against the view would be in a role in the database that has minimum permissions to call those stored procedures against a schema that contains views for BI purposes (things like power BI) and that role would not have permissions to do anything else. This will help protect the data in your DB by not exposing the base table names, prevent SQL injection vulnerabilities, and make your data updates into Power BI that much faster. If you are savvy , you could also design a stored procedure in a way that only returns incremental updates for faster data loads without having to do the complex setup for incremental data on the Power Bi side. Anyhow, that is probably a lot more detail and probably leads you to many more questions. I suggest reading up on star models, ERDs, normalization, cardinality, and grain as well as the use of views with carefully permissioned stored procedures (in their own schema so you can manage the security at that level rather than by individual views) to provide efficient and secure return of data from a DB into Power BI rather than just doing everything inefficiently on the power bi side or making up for data model deficiencies with DAX wizardry later.
@@jasonmurray8777 thank you, I always wondered that why would we need to do those major cleanup work before going into the visuals, when we usually are prepared for the visuals only after the guarantee that the available data is rock solid in the database/(whichever source)..
If anyone wants to learn with authenticity Microsoft Excel, Chandoo will be game changer for you..highly recommend for learning MS excel concepts from scratch.
Thank you so much for your generosity, knowledge, and experience. I enjoy your teaching style which is slow-paced and thorough. It is excellent for beginners like me. Much appreciated.
Thanks Chandoo! I share with you the passion for Excel and particularly power query. My data cleaning skills started with copy and pass ( like your shorts " Isaw my boss doing this"), then, I discovered Excel formulas and functions and worked with the basics ilke right, left and mid for text then nested formulas/functions combining them with len, substitute etc for more efficient data cleaning and transformation but was sort of manual too. The game change emerged when I discovered power query, and since that time, things changed significantly. Now, I fear no more data consolidation, cleaning, and transformation with full automation. Because of the power query, I still stick to Excel as my first data analytics option. Thanks for the great content... I hope you would consider more content on another Excel power.. The power pivot Stay blessed
Chandoo - These videos are fantastic. I follow a few YT channels whose niche charges are Microsoft “services” they are great. However, you are killing it with the micro-niche of Microsoft Excel. Keep up the incredible work. Your channel has been instrumental. Thank you.
Totally agree. It's an essential ETL tool. DAX is aggregation, but those who deal with the real world know how many errors, omissions, and data overlaps one needs to resolve before doing any meaningful analysis
It is a fantastic tool have been using it for almost two years now and it has made things possible that I would have had to use Access for instead. The file combining feature is, I think the easiest way to combine similar files from a single file folder. Way back when I did the same thing for Access I had to use a good bit of VBA code to do the same thing that Power Query did in less than 10 clicks of the mouse. Love your videos Sir, keep up the good work
Hi Chandoo. Can you please show a video of how to use power query for Mark Analysis and Teacher Analysis report and charts. Your videos are greatly inspiring and I've been learning so much from them. As a teacher trainer, I'm quite fascinated by the way you explain things and I m excited to know how I can use Power Query and Power BI to generate performance of students
My absolute favourite learning channel. I will have to check out your training courses on your site - your YT vids inspire me to work with data!! Sincerely much appreciated!!!
Thanks to you, I am learning so much about Excel. With my new job they do not provide the best training, so to have this and all these wonderful videos has helped me a great deal. Thank you, Chandoo!
I am a beginner to learn about Power Query and your video is easy to understand and follow your steps. When dealing with blanks, if a row is not blank all the way and is deleted, all information in other columns across the row will also be deleted. Will that be a concern when cleaning up the data? Thanks.
I am big fan of your channel, I love that, I am in the data analyst training, I would love to understand more use DAX tricks too, it seems a bit harsh to study!
Tip: Disable the background refresh on the query. I noticed my pivot tables were still showing old data after hitting "refresh all." Disabling background refresh on the query will force the pivot tables to wait for the query to complete before they refresh and you will no longer have to refresh to pivot tables separately.
Wow, interesting! Actually I like to put refresh pivots later because I can trank what are changes done and can easily undo/redo then, but still nice to know! :)
Power query's very useful as the intermediate step in between data e datavis, if you don't know good sql, your (power) querys, so as yours BIs, will be as performatic as a potato, when you're working with big datasets. Also, everything power query can do, dax can do better and more performatic, obviously once you load your raw, but clean, data with power query. As for me i'll confess once i started use sql and dax, power query only serves for me to load the data when not using direct query.
hello brother. Just one request. You are expert in excel. Please do make Excel tutorials for beginner. Yes there are lot of resources available but I believe if you do it it will definitely cover everything. Thanks from Nepal.
Chandoo. I really appreciate your channel. I’m learning a lot as I’m wanting to become a DA. I was wondering. How important is it to learn SQL? It seems Excel and Power BI alone is enough for data analysis.
Good video to show the power of Power Query. It definitely facilitates data cleaning, be it in Excel or Power BI. I wish it worked with big data. It tends to get really slow as the data size increases. But overall, it is a good tool to have.
Great video. I use Google Sheets/BigQuery and Data studio on a regular basis and this looks very similar to what I can achieve using the above mentioned tools Could you make a comparison video stating the differences and why one should learn to use the Microsofts suite of tools over Google?
Many shortcuts can’t work in sheets which we have in excel and The features of inbuilt library like automated functions are broad in excel compared to sheets.
Hi Chandoo!! Thanks a lot for this. You have been helping me a lot with PQ. I have prepared a data using PQ which has more than 10k rows and it takes good time to refresh the queries. Is it normal or is there any way to reduce the time? Please note that i have already tried disabling background preview option but still no change. Please help 🙌🙌
The time taken by PQ depends on what you are doing in the steps and the order you are doing them. I suggest removing the steps one at a time to figure out which one is causing the bottleneck. Then restructure your transformations so that the steps happen faster. Sometimes, delays can be caused by slow data sources (such as databases with weak servers or web sources that have long wait times)
Hi @@chandoo_ thank you so much for replying.. I checked and realised its the same step which i was suspecting too. I basically had to do a lookup. Therefore, i merged the query and while expanding the column i had to expand almost 8 columns from another table. This is what taking most of the time. Is there anyway to improvise the step? Please advise .. much appreciated!! ❤️
Merges are costly operations. You can leave the tables as they are and set up a relationship in Power Pivot (for both Excel and Power BI) if you don't need the physical column.
By simply removing the empty payments, you ignore the situation where one student enrolled to more than one course, fully paid for one but didn't pay for another. By removing empty course names you ignore the error of the school operator and you can have a situation where the student made a payment but the operator forgot to write the course name. By removing the duplicates you ignore the situation where one student made multiple partial payments to one course but in total should be fully paid. Can I be a data analyst now and earn $$$$$?
Agree with you Chandoo. As data is growing and becoming complex, it will carry some legacy issues and new ones too, so ability to curate and clean it, is going to matter. Thank you for a simple yet powerful tutorial!
You are welcome Vijay.
Before removing duplicates, it is best to handle the letter cases first. Power Query treats lower case and upper case of the same letter as different characters. I would transform all email addresses to lower case before removing duplicates 😉.
Great video, Chandoo!!
Amazing tips @Melanieondemand
Donut for you. 🍩
With removing duplicates, one could also remove partial payments, in case the administrators entering new students don’t pay attention to duplicates or made typos
Great tip - thank you!!
Just one Question:
What if a student has paid 100 and next time 200, but both these entries are recorded separately so while removing duplicates his 1 entry will be deleted. So how can we counter that?
Chandoo - Your Excel tutorials are well above par and I have learned so much watching your videos. I've been able to impress myself and my colleagues with your tips and tricks. I'm not a data analyst - but data defines my actions and you have introduced me to so many functions and tips that make a HUGE difference in time and pinpointing issues. Very awesome and very well done in every respect. Thank you, you are appreciated.
Oh, my girlfriend - she said she loves you hair!
Hi. Totally agreed. Power Query is amazing. Level of knowledge required to start is minimum, building skills has you go. Power Pivot data model and DAX is also amazing. And ensures extremely complex task can be developed and repeated without fuss. Martin
Vba is better)
Where to find jobs?.
I use VBA but only basic coding. Applying protect / unprotect workbook containing hundreds of worksheets. Not looked at VBA to ETL data.
@@Kirill628 yea vba is better, but it'd have took me 20 minutes of writing a code & debugging it to come to the same result that he did without writing a single line of code.
You can also create a folder that will automatically format itself if you have other raw data, say a different school but need the same analysis so you can drop the file into the power query folder and it will format everything so you don't have to repeat the steps
How to do this?
How do u do that?
Agreed. Just have to ensure all the column name remains the same
Awesome lesson Chandoo! Even in retirement, I continue to extensively use Excel & your Power Query videos have opened a whole new realm of possibilities for my Excel needs. Thank you!
Thanks GS for your passion to learn and love for Data. :)
Hi, is there any opportunities for Business Intelligence/ Data Analysis in Canada?
@@Awachit1 India mein he karle bro jitna waha par in hand milta hai ussey zyada he India mein milta hai. Agar bahar jaana hai toh Germany kya
If Data is small Power query, Else: SQL is your friend for large DATA sets......thanks Chandoo...Nxt video Pls compare MsExcel=>Power Query==> SQL....Pros & Cons, Industry Requirement.....
I use a lot of Power Query because I make a lot of Power BI dashboards. I spend a lot of time building star schemas - sometimes normalizing, sometimes not - it all depends on what I am trying to do with the data. The point is that I consider it to much more important to model the data for utility than the time I spend on any DAX I might write later. Honestly, its like 80% data modeling and 20% adding visualizations and some measures of calculated tables with DAX later. That said, I recently started going back to pre-formatting more significant transformations in SQL Server rather than trying to clean it up in Power Query. Loading some data into a data mart first to transform it with SQL, and then creating a view I can pull from into Power BI, is often a game changer for not having to do so many transforms in Power BI with Power Query. Put SQL and Power Query altogether and you can provide some very fast loading and amazing data visualization and reports with the Power BI report app too (you know - SSRS 2.0 for the cloud).
Thank you for this. This completely resonated with me. 💕
Hi @Jason Murray can you please elaborate it a bit so that a new data student like me will have good insight about the process you have mentioned.
@@saurabhdwivedi5866 Creating a good data model, most typically a star schema with a central fact table (or tables) surrounded by dimension tables (the things you will use as filters or slicers) is far more important than what you will do with DAX later. You need to understand the implications of any data model you create because if you don't you will often find yourself going back to the data model and possibly starting from scratch because you have painted yourself into a corner and no amount of DAX will get you out of it. That said, the model you choose will be affected by what existing relationships and cardinality exist within your data as well as your requirements for grain (granularity/precision) and, when very large data sets are involved, balancing those grain requirements against speed requirements. That said, my advice in this respect is, unless you have data that has already been modeled and has a data dictionary (or you are only working a single table or two), then you need to spend some time profiling your data when you start and make an entity relationship diagram (ERD). That ERD will most likely highlight for you what you can and can't do with your data and often leads you to realize what other data you need, but might not have, in order to meet requirements in the ultimate dashboard. With respect to my comment about using SQL first. You will likely often use data from a SQL database using an ODBC connection to pull it into Power BI. A database is typically a lot faster than Power BI and having pre-modeled data coming over from the SQL side will save you a lot of modeling on the Power BI side. Typically what you would do is create a view (or views) on the database side that have your data already as close to perfect as possible so you don't have to spend time cleaning it up in Power BI. Next, you would have a parameterized stored procedure to call against that view, even if the parameter was as broad as * (select everything). Accounts calling against the view would be in a role in the database that has minimum permissions to call those stored procedures against a schema that contains views for BI purposes (things like power BI) and that role would not have permissions to do anything else. This will help protect the data in your DB by not exposing the base table names, prevent SQL injection vulnerabilities, and make your data updates into Power BI that much faster. If you are savvy , you could also design a stored procedure in a way that only returns incremental updates for faster data loads without having to do the complex setup for incremental data on the Power Bi side. Anyhow, that is probably a lot more detail and probably leads you to many more questions. I suggest reading up on star models, ERDs, normalization, cardinality, and grain as well as the use of views with carefully permissioned stored procedures (in their own schema so you can manage the security at that level rather than by individual views) to provide efficient and secure return of data from a DB into Power BI rather than just doing everything inefficiently on the power bi side or making up for data model deficiencies with DAX wizardry later.
@@jasonmurray8777 Thankyou soo much. For sure I will go through the above mentioned point.
@@jasonmurray8777 thank you, I always wondered that why would we need to do those major cleanup work before going into the visuals, when we usually are prepared for the visuals only after the guarantee that the available data is rock solid in the database/(whichever source)..
If anyone wants to learn with authenticity Microsoft Excel, Chandoo will be game changer for you..highly recommend for learning MS excel concepts from scratch.
Thank you so much for your generosity, knowledge, and experience. I enjoy your teaching style which is slow-paced and thorough. It is excellent for beginners like me. Much appreciated.
Thanks Chandoo!
I share with you the passion for Excel and particularly power query. My data cleaning skills started with copy and pass ( like your shorts " Isaw my boss doing this"), then, I discovered Excel formulas and functions and worked with the basics ilke right, left and mid for text then nested formulas/functions combining them with len, substitute etc for more efficient data cleaning and transformation but was sort of manual too. The game change emerged when I discovered power query, and since that time, things changed significantly. Now, I fear no more data consolidation, cleaning, and transformation with full automation. Because of the power query, I still stick to Excel as my first data analytics option.
Thanks for the great content... I hope you would consider more content on another Excel power.. The power pivot
Stay blessed
Such a versatile tool, I discovered power query by accident years ago and have found new uses for it nearly every month since.
Best kind of explanations I ever seen in RUclips. Good job 👍🏻
I've never used Power Query in my life, and you made this process clear and rather fun. Thank you and I'm happy to subscribe to your channel!
Welcome aboard Mark. I am glad you enjoyed this.
Chandoo you are simply too much. I'm short of words. Thanks a lot.
You are most welcome
Hum, Chandoo is in another dimension more than the humain! I want to learn from you Sir and get my first job as a junior data analyst si possible.
0:11 focus on PowerQuery, thank you Sen Sei!
0:36 PQ is a data pre-processing engine
Every time you come with Valuable content. Thank you sir
Love You chandu Sir From Uttarpradesh Amethi, Jay hind Wande matram 🇮🇳🙏
All your videos are worth watching.. we end up learning something new always...thank u so much for sharing your knowledge..😊
Chandoo - These videos are fantastic. I follow a few YT channels whose niche charges are Microsoft “services” they are great.
However, you are killing it with the micro-niche of Microsoft Excel. Keep up the incredible work. Your channel has been instrumental.
Thank you.
So glad to hear that JBF. Thanks for your love and support.
Totally agree. It's an essential ETL tool. DAX is aggregation, but those who deal with the real world know how many errors, omissions, and data overlaps one needs to resolve before doing any meaningful analysis
Better data → Easier DAX. That is why PQ is the key to successful data analysis.
Hi Chandoo, please continue such series of Power Query. Your way of teaching is amazing 🤗
It is a fantastic tool have been using it for almost two years now and it has made things possible that I would have had to use Access for instead. The file combining feature is, I think the easiest way to combine similar files from a single file folder. Way back when I did the same thing for Access I had to use a good bit of VBA code to do the same thing that Power Query did in less than 10 clicks of the mouse.
Love your videos Sir, keep up the good work
I have been learning since 2013 with yours help ,thanks
Hi Chandoo. Can you please show a video of how to use power query for Mark Analysis and Teacher Analysis report and charts. Your videos are greatly inspiring and I've been learning so much from them. As a teacher trainer, I'm quite fascinated by the way you explain things and I m excited to know how I can use Power Query and Power BI to generate performance of students
Good points Shanmugapriya. I will add a video on this topic in upcoming months.
Excellent presentation. I hope to use this to transfer and analyse some data held in multiple pdf’s
My absolute favourite learning channel. I will have to check out your training courses on your site - your YT vids inspire me to work with data!! Sincerely much appreciated!!!
Awesome, thank you!
you are such a good trainer. your ways of making understand others are very nice. very knowledgeable video.
Thanks to you, I am learning so much about Excel. With my new job they do not provide the best training, so to have this and all these wonderful videos has helped me a great deal. Thank you, Chandoo!
You have made the complex simple to follow and understand. Thank you very much.
I am looking for a job I have experience in ms excel for 4 years kindly advise us every day, watching your videos
Nice presentation with no nonsense
Thank you so much, from the date I found your Channel, I admire data cleaning and analyzing it in pivot tables. Thank you once again
Where did you start from? He has a lot of videos.
thank you chandoo sir....making things awesome
Thank you Sir, good explanation, please upload more and more videos.
Thanks! 8:22 it is usually good to check how many duplicates there are using View - Data Preview
I am a beginner to learn about Power Query and your video is easy to understand and follow your steps. When dealing with blanks, if a row is not blank all the way and is deleted, all information in other columns across the row will also be deleted. Will that be a concern when cleaning up the data? Thanks.
Thanks Chandoo! PQ is so amazing, I am still learning and have only scratched the surface of how to use it
Is it just me or does Chandoo have a soothing therapeutic voice?
@Chandoo you are always good. I didn't even finish the video, and I already like what I'm learning. Great video and thank you.
This is soooooo cool and understanding. Very slow and explainable. Thank you sir
Your an awesome tutor bro!. thank you so much.
You are just amazing! I was able to follow you 100%. Keep up your good work.
That shirt! 😀 I appreciate your teaching and humor, Chandoo!
Hello Chandoo i have seen your RUclips content and i love that its awsom and unique. thankyou your doing well 👏👏👍😇
Excellent power query logic will help both Excel and Power BI
I am big fan of your channel, I love that, I am in the data analyst training, I would love to understand more use DAX tricks too, it seems a bit harsh to study!
Tip: Disable the background refresh on the query. I noticed my pivot tables were still showing old data after hitting "refresh all." Disabling background refresh on the query will force the pivot tables to wait for the query to complete before they refresh and you will no longer have to refresh to pivot tables separately.
Wow, interesting! Actually I like to put refresh pivots later because I can trank what are changes done and can easily undo/redo then, but still nice to know! :)
Thank you Chandoo Jee. Thanks for sharing your knowledge and experience.
Thanks for making it simple😊😊
Gurudev 🙏this one is absolutely necessary one for us & you put it in an awesome manner like always....Thanks a lot
Such incredible and inspiring content for datasets. Thank you, sir
Amazing knowledge. Thanks for sharing Chandoo
You are a great teacher Sir.. This tutorial has changed a lot in my working.. I bow down to you 😢🙏🙏❤
You are very welcome
thank you so much for the very quickly useful and informative lesson
Amazing video as alwyas sir. I just have one doubt. what is your take on IBM SPSS as a data analysis software ?
Power query's very useful as the intermediate step in between data e datavis, if you don't know good sql, your (power) querys, so as yours BIs, will be as performatic as a potato, when you're working with big datasets. Also, everything power query can do, dax can do better and more performatic, obviously once you load your raw, but clean, data with power query. As for me i'll confess once i started use sql and dax, power query only serves for me to load the data when not using direct query.
Thank you for this great tutorial/walkthrough, really helpful and just the right speed!
Excited and waiting for your valuable content sir❤️
Today, this is your 3rd video which I am watching and learning from.
Such incredible and inspiring content for data lovers. Thank you, sir, 💙
hello brother. Just one request. You are expert in excel. Please do make Excel tutorials for beginner. Yes there are lot of resources available but I believe if you do it it will definitely cover everything. Thanks from Nepal.
Hi Bijaya, There are a few videos in the playlist of this channel, u can have a look and learn from them.
Please see the Getting started with Excel tutorial. ruclips.net/video/F7aPazuS8QY/видео.html
Thanks a Ton sir. I had enrolled for course on PQ, but guy had made it so boring and untasteful.
You just made it so interesting 🙏
You're fabulous sir.... Much much respect...👏👏
I found you the finest and easiest mentor. .. Love your teaching pattern
It is so informative and helpful …many thanks, chandoo
we are waiting for it till now, Thank you.
Very good explanation.the details sir
Another excellent video with clear explanations and examples.
Glad you liked it!
Thank you for this excellent power query session, you alwasy make it look easy & understandable. :)
Thanks buddy 😊
Great video with great explanation.
very useful, thank you so much
Chandoo. I really appreciate your channel. I’m learning a lot as I’m wanting to become a DA. I was wondering. How important is it to learn SQL? It seems Excel and Power BI alone is enough for data analysis.
Great video..wants more videos like this.
♥ from Bangladesh
Thank you Chandoo for educating us
Good video to show the power of Power Query. It definitely facilitates data cleaning, be it in Excel or Power BI. I wish it worked with big data. It tends to get really slow as the data size increases.
But overall, it is a good tool to have.
So for big data... What are the options? I mean VBA or any other tool we have? Thanks.
That t-shirt is fire!!!
Very detailed explanation ❤
I agree that PQ does it all however most data analyst jobs still require SQL and/or Python.
Anna Nee videos next level untaayi anna
you are awesome Mr. Chandoo.....Chandoo Bhiya here Deepak
great job Chandoo¡¡ you have created a really interesting channel ¡¡¡
Hello This awesome Kudos! Chandoo Great thanks a lot. Sure will love to learn more about power query.👍🏼🙂
Great video.
I use Google Sheets/BigQuery and Data studio on a regular basis and this looks very similar to what I can achieve using the above mentioned tools
Could you make a comparison video stating the differences and why one should learn to use the Microsofts suite of tools over Google?
Many shortcuts can’t work in sheets which we have in excel and The features of inbuilt library like automated functions are broad in excel compared to sheets.
more power to you Chandoo, thanks for this video
Very nice video i have seen so far ...make dex and m codes like that simple way for laymens
Chandoo, thanks for your advice with examples. Great tool, and I have already started to learn it. A question: why do you have 2 keyboards? 😁
Thank you so much. I am watching your videos and becoming awsome in Excel
7:00 select..remove empty is better untick null, blank
You are a great teacher!
Hi Frnds,
Does Mac version of Office Excel have Power Query or Power BI?
Thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!! kindly make more such videos
Hi Chandoo!!
Thanks a lot for this. You have been helping me a lot with PQ.
I have prepared a data using PQ which has more than 10k rows and it takes good time to refresh the queries.
Is it normal or is there any way to reduce the time?
Please note that i have already tried disabling background preview option but still no change. Please help 🙌🙌
The time taken by PQ depends on what you are doing in the steps and the order you are doing them. I suggest removing the steps one at a time to figure out which one is causing the bottleneck. Then restructure your transformations so that the steps happen faster.
Sometimes, delays can be caused by slow data sources (such as databases with weak servers or web sources that have long wait times)
Hi @@chandoo_ thank you so much for replying..
I checked and realised its the same step which i was suspecting too.
I basically had to do a lookup. Therefore, i merged the query and while expanding the column i had to expand almost 8 columns from another table.
This is what taking most of the time.
Is there anyway to improvise the step? Please advise .. much appreciated!! ❤️
Merges are costly operations. You can leave the tables as they are and set up a relationship in Power Pivot (for both Excel and Power BI) if you don't need the physical column.
Thanks again, Sir!... Am an avid fan....
It Helps man !! Thanks for the video
By simply removing the empty payments, you ignore the situation where one student enrolled to more than one course, fully paid for one but didn't pay for another. By removing empty course names you ignore the error of the school operator and you can have a situation where the student made a payment but the operator forgot to write the course name. By removing the duplicates you ignore the situation where one student made multiple partial payments to one course but in total should be fully paid. Can I be a data analyst now and earn $$$$$?
So good! Thank you Chandoo!!!
Please do more such videos like trend skills other that coding which can tend to grab a job for non technical job holders
Simple and more powerful✅