Learn Power Query & Automate Boring Data Tasks in 15 Minutes!

Подписаться 609 тыс.

Просмотров 317 тыс.

50% 1

If you have time to learn just one skill in 2023, make it POWER QUERY. This single technical skill can help you make massive gains in your data analysis work.
💥 Get my Power Query mini-course - chandoo.org/wp/power-query-po...
WHAT IS POWER QUERY?
Power Query is a data pre-processing engine built into Excel and Power BI. You can use it to bring data from anywhere and do pre-processing or clean-up activities with ease. It lets you automate common data tasks like removing duplicates, combining files, merging (joining) datasets, adding derived columns, splitting or extracting data and changing the shape / size of your data.
HOW TO GET POWER QUERY?
It is already there in Excel and Power BI.
In Excel - Go to DATA Ribbon and use the "Get & Transform Data" options.
In Power BI - use the "Get data" or "Transform" buttons.
IS POWER QUERY BETTER THAN SQL?
Power Query and SQL have largely similar capabilities. But Power Query gives you more freedom and options. Plus, it is easier to learn (in my opinion).
~
IN THIS VIDEO ⏱️👇🏼
=================
0:00 - One skill you should learn in 2023 - Power Query, what is it?
2:02 - How to activate & use Power Query?
4:43 - STEP 0: Pre-cleaning the data
6:31 - STEP 1: Dealing with Blank data points
7:48 - STEP 2: Removing the duplicates
8:30 - STEP 3: Generating ID values
11:05 - STEP 4: Calculating Course Date from Registration Date
13:25 - STEP 5: Figuring out the Fee Status
15:39 - How to load the cleaned data back to Excel?
16:19 - Analyzing the Power Query data and Refreshing
📂 DOWNLOAD PRACTICE FILES
===========================
Sample raw data file - chandoo.org/wp/wp-content/upl...
Completed workbook (with Power Query steps) - chandoo.org/wp/wp-content/upl...
💻 LEARN MORE about POWER QUERY
================================
Check out below videos, playlists & articles for more on Power Query.
▶ COURSES:
▶ ▶ Get my Power Query mini-course and learn all you need in one place quickly - chandoo.org/wp/power-query-po...
▶ ▶ Join Excel School program to learn Power Query, Power Pivot, Formulas, charting & Dashboards in one place - chandoo.org/wp/excel-school-p...
▶ VIDEOS:
▶ ▶ Data cleaning in Excel - 10 tips (Power Query, Formulas & Flashfill) - • Data Cleaning in Excel...
▶ ▶ Power Query in Excel (Beginner to ADVANCED) - • Power Query Tutorial -...
▶ ▶ How to combine data from files (using Power Query) - • Powerful trick to comb...
▶ PLAYLIST:
My Power Query videos playlist - • Power Query Tutorial -...
▶ READ:
What is Power Query and how to use it (with 4 examples, sample files) - chandoo.org/wp/power-query-tu...
~
Happy learning. I wish you more POWER ⚡ in 2023
#PowerQuery #excel

Наука

Опубликовано:

2 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 279

@vijayarjunwadkar Год назад

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!

@chandoo_ Год назад

You are welcome Vijay.

@kaitlinbero1894 Год назад

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!

@bj_ Год назад

Such a versatile tool, I discovered power query by accident years ago and have found new uses for it nearly every month since.

@MelanieOnDemand Год назад

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

@chandoo_ Год назад

Amazing tips @Melanieondemand Donut for you. 🍩

@florisvanderleest7798 Год назад

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

@Nic-xr1og Год назад

Great tip - thank you!!

@malwalsabino519 Год назад

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

@Bethwel1 Год назад

You have made the complex simple to follow and understand. Thank you very much.

@chrism9037 Год назад

Thanks Chandoo! PQ is so amazing, I am still learning and have only scratched the surface of how to use it

@shafimirza8551 Год назад

All your videos are worth watching.. we end up learning something new always...thank u so much for sharing your knowledge..😊

@reazahmed7004 Год назад

You are just amazing! I was able to follow you 100%. Keep up your good work.

@normanknutsen8253 Год назад

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

@GoGoTwice Год назад

Thank you for this great tutorial/walkthrough, really helpful and just the right speed!

@Sandroider Год назад

Thanks! 8:22 it is usually good to check how many duplicates there are using View - Data Preview

@pampapathipampareddy9835 Год назад

I have been learning since 2013 with yours help ,thanks

@kamaur01 Год назад

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

@MrDhunpagla Год назад

Gurudev 🙏this one is absolutely necessary one for us & you put it in an awesome manner like always....Thanks a lot

@ktp. Год назад

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

@chandoo_ Год назад

Awesome, thank you!

@mohdayazuddinshaikh4454 Год назад

Hi Chandoo, please continue such series of Power Query. Your way of teaching is amazing 🤗

@dcoltonbrown Год назад

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.

@dcoltonbrown Год назад

Oh, my girlfriend - she said she loves you hair!

@niladribiswas7667 Год назад

Such incredible and inspiring content for data lovers. Thank you, sir, 💙

@shaikjalal1274 Год назад

Hello Chandoo i have seen your RU-vid content and i love that its awsom and unique. thankyou your doing well 👏👏👍😇

@SaleemKhan-hm2br Год назад

Thank you Chandoo Jee. Thanks for sharing your knowledge and experience.

@morrismaina1413 Год назад

You are a great teacher!

@mitch6912 Год назад

Amazing knowledge. Thanks for sharing Chandoo

@cassiemeyer1164 Год назад

So good! Thank you Chandoo!!!

@ahmedabdulahimahamud3272 Год назад

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

@ifoodieTV 2 месяца назад

Where did you start from? He has a lot of videos.

@pgkannan Год назад

Excellent power query logic will help both Excel and Power BI

@sumakudala688 Год назад

Every time you come with Valuable content. Thank you sir

@avishekd4229 Год назад

Thank you Chandoo for educating us

@TimeManInJail Год назад

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

@preetamrajpenugonda4552 7 месяцев назад

How to do this?

@Horn_of_Afrika 6 месяцев назад

How do u do that?

@GoldenSlumber474 Год назад

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!

@chandoo_ Год назад

Thanks GS for your passion to learn and love for Data. :)

@Awachit1 Год назад

Hi, is there any opportunities for Business Intelligence/ Data Analysis in Canada?

@Aryanthakur-yt9bc Год назад

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

@markslima1557 Год назад

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!

@chandoo_ Год назад

Welcome aboard Mark. I am glad you enjoyed this.

@GuyOrlov Год назад

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!

@suvamtechnical 2 месяца назад

you are such a good trainer. your ways of making understand others are very nice. very knowledgeable video.

@mirateequrrahman8478 Год назад

You're fabulous sir.... Much much respect...👏👏

@niteeshmishra2790 Год назад

Love You chandu Sir From Uttarpradesh Amethi, Jay hind Wande matram 🇮🇳🙏

@kwesikwesi4261 Год назад

This is soooooo cool and understanding. Very slow and explainable. Thank you sir

@ManishKumar-jx4eg Год назад

Thank you for this excellent power query session, you alwasy make it look easy & understandable. :)

@learningwithmaliha 2 месяца назад

Amazing video, thanks Chandoo!

@napstervn Год назад

thank you so much for the very quickly useful and informative lesson

@cathappytimes Год назад

Thank you Chandoo.

@adsonnet Год назад

we are waiting for it till now, Thank you.

@dushanthan50 Год назад

It Helps man !! Thanks for the video

@futurehopeobioma670 11 месяцев назад

Chandoo you are simply too much. I'm short of words. Thanks a lot.

@chandoo_ 11 месяцев назад

You are most welcome

@martinpaulbannister1745 Год назад

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 Год назад

Vba is better)

@chandramohan1418 Год назад

Where to find jobs?.

@martinpaulbannister1745 Год назад

I use VBA but only basic coding. Applying protect / unprotect workbook containing hundreds of worksheets. Not looked at VBA to ETL data.

@infamous8541 Год назад

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

@johnford3825 3 месяца назад

Excellent presentation. I hope to use this to transfer and analyse some data held in multiple pdf’s

@rahulranjan8511 Год назад

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.

@rjbanavar Год назад

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 🙏

@AmyhoGeokmui 8 месяцев назад

It is so informative and helpful …many thanks, chandoo

@anandl2003 Год назад

Hello This awesome Kudos! Chandoo Great thanks a lot. Sure will love to learn more about power query.👍🏼🙂

@culpritdesign Год назад

Data modeling and transformation is important. 😊 great content

@destinationsuccess27 Год назад

Thank you for this video.

@faizankirmani6266 Год назад

Lovely demonstration

@lokeshr9225 Год назад

Excited and waiting for your valuable content sir❤️

@shubh.vaishnav 6 месяцев назад

Thanks buddy 😊 Great video with great explanation.

@anwarmohdsha 4 месяца назад

more power to you Chandoo, thanks for this video

@user-ke7vq9bg7q 5 месяцев назад

Such incredible and inspiring content for datasets. Thank you, sir

@sadaedost Год назад

Excellent demonstration :)

@hemanthkumar4870 Год назад

Wow...its amazing chandoo..

@creativeblock377 19 дней назад

Very detailed explanation ❤

@chandreshslal9686 Год назад

Thanks for sharing your know skills

@gonzalofuste Год назад

great job Chandoo¡¡ you have created a really interesting channel ¡¡¡

@fluffyhoneytina Год назад

Thank you so much.

@lavon4111 Год назад

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.

@SyedSalmanhussain21 Год назад

Respect for sharing useful info!!

@sairamadaboina8257 5 месяцев назад

Thank you Sir, good explanation, please upload more and more videos.

@mehulmandirwala2318 Год назад

Thanks for sharing 👍

@Ringsofsweden Год назад

Wonderful information. Thanks alot

@7dx549 Год назад

Simple and more powerful✅

@GeorgeAJululian Год назад

Very helpful and useful many thanks

@MrPapiBanda Год назад

Great video..wants more videos like this. ♥ from Bangladesh

@mamalik1088 Год назад

Always nice explained SIR C

@kiranmedishettitalks594 Год назад

Thank you chandoo

@storiesshubham4145 Год назад

Superb video Sir 👌

@mantis1787 Месяц назад

That shirt! 😀 I appreciate your teaching and humor, Chandoo!

@awesomeraj953 Год назад

Today, this is your 3rd video which I am watching and learning from.

@deepakverma79998 Год назад

you are awesome Mr. Chandoo.....Chandoo Bhiya here Deepak

@bodyart1460 Год назад

Thank you so much sir

@zaafirc369 Год назад

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.

@ganeshn3800 Год назад

So for big data... What are the options? I mean VBA or any other tool we have? Thanks.

@markreichard2144 Год назад

Another excellent video with clear explanations and examples.

@chandoo_ Год назад

Glad you liked it!

@neeleshgupta8886 Год назад

You are true gem 💎 sir ❤️👍

@rameshh9560 5 месяцев назад

Nice presentation with no nonsense

@asifkhurshid9208 Год назад

well explained, thanks and regard.

@Soulenergy31 Год назад

0:11 focus on PowerQuery, thank you Sen Sei! 0:36 PQ is a data pre-processing engine

@shanmugapriyayu2141 Год назад

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

@chandoo_ Год назад

Good points Shanmugapriya. I will add a video on this topic in upcoming months.

@evilpapa6013 Год назад

Thank you for this

@moisestatis5418 Год назад

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

@melvindias1249 Год назад

Good job once again.

@Hurmali1 Год назад

Thank u Chandoo

@behrad9712 11 месяцев назад

Thanks Thanks!

@saisundaranthirugnanam5051 Год назад

Superb sir

@charlidavid5592 Год назад

Awsome Chandoo..!

@shaikusman536 Год назад

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

@vinaykumarnr2533 Год назад

nice video😊 feel easy to learn

@justbeingfrank6342 Год назад

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.

@chandoo_ Год назад

So glad to hear that JBF. Thanks for your love and support.

@rajkumarpalle8217 Год назад

Thank you...

@ilyakabanov1820 Год назад

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

@chandoo_ Год назад

Better data → Easier DAX. That is why PQ is the key to successful data analysis.