Power Query - Beginner to PRO Masterclass in 30 minutes

[@suprajm3117]

Liked how you added gif when you run the functions, inner feelings of beginners, awesome teacher

[@confidential303]

please dont use gifs is only distracting ..though this was not that disturbing, generally I find it more childish. btw, nice explanation!

[@236715238221]

Guruvaaram naatiki Guru’gariki vandanamu🙏 I keep watching your videos for the past few weeks and it has helped me to elevate my knowledge in Excel to a different level. I am 52 years old and your videos give me enthusiasm to try new solutions at my work. Thanks Chandoo garu🙏 Kiran from Singapore

[@secret_step]

Chandoo sir how can you be so good at explaining things. Genuine thanks for sharing such informative content. I mean I learn 90% of data things from you. Thanks again

[@deepikaanjali8646]

00:05 Power Query is a time-saving data cleaning and transformation tool in Excel and PowerBI. 02:33 Connect to live data sets using Power Query in Excel. 07:09 Transforming header and data cleaning 09:21 Using the fill down operation to handle merged cells and remove nulls in Power Query. 13:58 Setting up web scraping with Power Query to get data from Wikipedia and showcasing its dynamic live connection 16:21 Importing and transforming data from Excel workbook using Power Query. 20:14 Transform data using Power Query options 22:07 Easily edit steps and handle data entry errors in Power Query. 26:00 Utilizing Power Query to manipulate and split data columns. 28:07 Splitting and manipulating values in columns with Power Query 32:14 Using conditional column for full-time part-time tagging 34:28 Refreshing data updates the information

[@faizasiddiq6069]

Chandoo sir it is mind blowing. I'm searching for some functions in excel but unable to find it now I introduced it in power query because of your in depth lecture. Thank you so much. May Allah bless you.

[@thebesttechnical3608]

Please never stop making videos. We need more teachers like you.

[@phanicekemunto3880]

Chandoo the master. I have been following your videos since I started learning Excel. You are the best instructor ever. Keep it up master

[@venkatiyer7459]

thanks again guru .... I tried with Olympics 2024 medal .... after loading the data I observed ‡ was also present in the data table ... too good to learn with you 🙏

[@abhishekprabhu1]

That's really really an awesome experience to learn from you Sir, the way you teach or explaining the course is totally awesome to listen and learn from you. I almost thought to quit about data analysis, but you have made it very interesting and thrown much light into it, made the complexity into way more easier, i got my passion back, I struggled a little with SQL, but i hope if i could put an extra effort into it, i will sooner grasp that too. Thank you Sir, God bless you and your family.

[@BalakishanGundi]

Mr. Chandoo! You are a Gem in the Teaching world!Thank you very much!

[@charlesstoy5411]

Excellent presentation. You provide concise but in-depth explanations very clearly. You break down complex ideas into simple and easy-to-follow steps. Found my instructor for data analysis classes. Thank you!

[@sathyaspace]

Man Chandoo just out of the world explanation...can't express my gratitude in words for the knowledge that I acquired using this video..the intriguing fact about the video was that you made 30 mins pass just as like 10 mins ,with you adept knowledge in the subject and engaging way of presenting the video. Please do keep up the great service that you are doing. Loads of love ❤

[@jasmineskitchen5532]

Thank you so much Chandoo for your dedication to teach people like us who wants to become a data analyst. I am so glad that I found your videos. You are the best teacher. Keep up the good work

[@chandoo_]

So nice of you

[@dindafitria6539]

Thanks a lot for sharing this awesome Power Query in Power BI video! It was super helpful, and I really appreciate all the effort you put into making it. Keep up the great work!!

[@allaboutpriya1996]

Don't know whether you will read it or not but... I really want to Thank you Sir for your every single video for Data Analyst 🙏🏻🙏🏻🙏🏻🙏🏻🙏🏻🙏🏻.... You don't even think of how people are getting help from this channel!... God bless you. Keep uploading more n more ❤🙏🏻

[@chandoo_]

So nice of you

[@srivanthimuralidharan4448]

True

[@onuohaemmanuel4382]

I have saved your name in my head for further references... So good in transferring information; thanks sir.

[@reem_intern_doctor]

You're such a brilliant teacher

[@the_physicist111]

Effectively helpful way of teaching ! Thanks ❤

[@Niranga1982]

Many thanks for the Knowledge Sharing! Keep it up the good work. Cheers

[@srivanthimuralidharan4448]

May god bless you sir, your teaching is really helpful sir

[@Official_Debex]

Thank you sir I really got value ❤power query

[@e8189]

Дуже інформативне відео. Дякую! Привіт з України.

[@tienngotk]

blow my mind, thank you so much.

[@MwendeTJ]

Lots of love to you. Thank you!!!!

[@RahulMandal-ub2zw]

Thank you so much sir, your explanation method is very well for beginner

[@srivanthimuralidharan4448]

Such a good video sir, really helpful sir, thanks a lot!!!1

[@sunmudoes]

can't wait for the next video!! I learned a lot from this video! Love the way you teach!

[@Qunikk]

I hope it will help me to get a first Data Analyst job. Much appreciated bro

[@JJ_TheGreat]

28:24 I don’t understand… Why is the “Last Name.2” column the middle name (after splitting)? It seems like “Last Name.2” should be the last name - but that would mean that splitting with the right delimiter doesn’t make sense (?) Please advise!

[@YogeshDalvi-c9t]

Yes I got same doubt. My Solution: First use left most delimiter Then trim first name column & use left most delimiter Finally you delete the middle column

[@Hariskhan0058]

Superb explanation Each and every point is neat and clearly understandable Thank you very much chandoo ❤

[@hari60700]

Just holding your hand take me to data analysis heavens 🎉

[@mekonnenassefabeyecha7724]

Blessings, Chandoo!

[@mirzajahanzaibvlogs]

Ma Sha Allah, very nice

[@jeevan7657]

Thank you so much for the lecture 😊

[@CuriousJo1122]

Great tutorial, thank you!!!

[@yhharishadyar7843]

Very good information sir, and very neatly explained

[@muhammadtasiu5533]

You are doing great sir

[@prabhulingpadadayyachaukim6434]

What a great teacher you are ! Thank you Chandoo.

[@Amangupta27998]

Loved your work.

[@goginenikotesh6458]

Excellent work!!

[@kritika2a]

thanku chandoo , youre really helpful

[@andreasandoval5039]

You are amazing, thank you very much 🙏🏻

[@aulia-m4h]

I LOVE YOU SIR

[@yallsyeager_og]

You are very good teacher sir.🙏🙏🙏🙏🙏🙏🙏🙏

[@best4you524]

Thanks a lot for providing such an informative tutorial sir🙏🏻🙏🏻

[@pratapd4589]

Awesome❤

[@navitakirar9361]

you are awesome brother

[@arbazahmad7177]

Excellent content..thanks for sharing 🎉

[@musariololade2204]

Many thanks for this lesson. I learnt so much. Wondering if you will also cover what the impact of AI will be on data analyst roles

[@Kevinroby2205]

This is a gem

[@drpupai]

Thanks a lot, learning a lot from you. You're really awesome. 😎

[@IzharMomin-q2q]

You Are Awesome...................

[@Gorman-84]

You are great. Thank you.

[@HaileysHomes-ix5yu]

you are Amazing 👏🙌

[@RelavantoBlog]

From Bangladesh, ❤❤❤❤❤

[@dr.samreenmir2694]

very useful video. Could you pls recommend me the software you are using to make the videos as you are making boxes, arrows etc. to indicate what you are teaching

[@macguyo]

Thank you for the tutorial.

[@Lavendergirl_8649dti]

thanks for the video

[@kperegbeyimabelegude9253]

Super intelligent!

[@skilly_data]

Thanks a lot sir ❤❤❤

[@mohammedshabaaz9625]

Thank You Chandoo

[@Laber19]

Chandoo, thanks for the great video. I have one question though. I have a source table in which one column header name has changed. The data fails to load when attempting to use that query again. I edited the name of the column in advanced editor. But, it fails to save that change for use in subsequent uses of the query. How can I save the edited version of the query?

[@greenvally8467]

When you change a column header in the source table and then encounter issues with data not loading in Power Query, it’s usually because Power Query is looking for the old column header name. Here’s how to resolve this issue: Open Power Query Editor: Go to the "Data" tab in Excel or the "Home" tab in Power BI and click on "Transform Data" to open the Power Query Editor. Check Column Headers: Look at the steps applied in the "Applied Steps" section in the Query Settings pane. You might find a step where the column headers are referenced. This is often the step that needs updating. Update Column References: If there is a step that references the old column names, you need to update it with the new column names. For example, if you have a step that renames columns or filters based on the column names, make sure those names are updated to match the new headers. Check Column Transformations: Go through the transformations applied to the columns. If any transformation step refers to column names, ensure they reflect the new headers. Refresh Preview: After updating the column names, refresh the preview in Power Query by clicking the "Refresh Preview" button. This will ensure Power Query is using the latest schema from your source. Reference from Chatgpt

[@maciejkopczynski55]

Another solution would be using a postion based reference to the column instead of its name. Table.ColumnNames(last_step_name){indexOfTheColumn}. Example: =Table.ColumnNames(ouput){0} would return a string - name of the first column of the output step. Note that Power Query like for example Python starts counting from 0 not 1, hence {0} results in the first element.

[@laptop0004]

THANK YOU SIR ❤

[@FitnessFoodRecipes]

Great video! What software are you using to record your screen and annotate it at the same time?

[@muhammadsadiq2330]

One question, If I want to convert this "medals" query (Timestamp14:38) in back Excel values by "Ctrl + Shift + V" to apply Excel formulas and to make data static for some reason, can we do it? especially for data obtained from sources other than Excel.

[@maciejkopczynski55]

If you are asking whether we can copy the output generated by the query to another location and pasting as values - yes we can. You can make the copy-pasted values static, which mean they will not update on query refresh. There will be no linkage between query output and copied values.

[@andrewdove342]

Hi Chandoo, I want to learn more about connecting excel to a SQL database, and how to setup the data in SQL, and how it comes through as a live connection in Excel. Is there any content on that process or anything which you can recommend? Thnks!

[@iclass8719]

PLEASE SUGGEST FREELANCE JOBS IN EXCEL

[@sjagadeesh79]

Assomee..❤ GURU garuuu....

[@PraveenKumar-il8kn]

Hi sir, can we read confidential labelled excel file in power query?

[@taizoondean689]

God Bless You

[@rajkiran5764]

Fan From TamilNadu❤

[@sandravanophemert61]

Once again... Big thanks for your great tutorials! I have already learned so much from you! But if I may be so bold to ask you a question... I have started using PQ and started to find my way working with it. It is really great, once you get the hang of it, and an absolute time saver. However, I work with large datasets and always get into trouble selecting/extracting information from a column, due to the 1000 rows limit. I have searched the internet, but cannot find the solution I seek. Do you know a way to avoid the 1000 rows limit (other then profiling the complete dataset, because this is not the solution for my problem)? Thank you in advance, and looking forward to your next tutorial!

[@hari60700]

I learned power query watching your previous videos .It's really a useful tool in excel

[@chrism9037]

Love these videos, thanks Chandoo!

[@donniemcgee7523]

You, Sir, are one of the best teachers! Thank you for sharing this lesson.

[@manjunathab8633]

Best 🎉

[@AccountsWoundedBuffalo]

Hello! That was very helpful thank you. :) Can I use power query to cross-reference data sets? For example if one has information on hours worked and another on rates charged for those hours worked.

[@chandoo_]

Yes, you can. These are called "merges". See chandoo.org/wp/power-query-tutorial/ and refer to 3rd example.

[@pjvarma]

Thank you ❤❤

[@PhanithReach]

How can I use the name of the current file as the record in power query?

[@chandoo_]

PQ offers a few options. - Excel.CurrentWorkbook to access the current file - Folder / File connection can give you the file name for the file you are connecting You can also use a cell in the current workbook to hold the file name (either manually or =CELL("filename"))

[@shivanigupta6442]

Too good!

[@sseemm]

Thank you Chan

[@sufimudi4524]

Hi Chandoo. Can you do analysis of duration using power query? For example duration of youtube for different titles and different authors and summary by authors. I have tried but can not come up with duration.

[@chandoo_]

You can do it easily. We have added "duration" in this video by calculating employee tenure. For videos, usually the duration is in minutes. You can easily analyze it in Power BI once the data is in place.

[@satishojhaMrblank]

Great Chandoo. it is very appreciate thanks a lot. 😊👍👌❤. Does power query get data from folder (100 excel workbook) and in one workbook there is 2 sheets and rest workbook only 1 sheet?

[@chandoo_]

You can use "Folder" query option to get data from all the files. See this example - kzbin.info/www/bejne/iXjdlpqjlp6Vmrs

[@franciscoparra7224]

Thank you for the video. I appreciate you taking your time to do this. How can I apply this method and extract data from a table in a webpage that requires a password?

[@chandoo_]

Unfortunately, PQ doesn't allow for password-based automation (yet). You have a few options: 1) use RPA tools like Power Automate to login to the website and navigate to the page, copy the data to a text file and then trigger the PQ refresh. 2) Manually extract the data to a file and then use that with PQ 3) Write some code (I prefer Python) to do the whole thing

[@JJ_TheGreat]

11:31 Ah, I see it! The bottom row is a subtotal row, which shouldn’t be included with raw data. Time to remove the bottom row!

[@harivinayak03]

Thanks Chandoo. This course is extremely helpful!

[@ACy629]

Sir - In the name split example, please check "Nazeer Basha Mustafa" it is taking out the last name not the middle name.

[@titiladeadegunolamide76]

Hello Chandoo, Thanks for the great video...Please could you recommend any website to get data sets to practice on? Thanks

[@apppu1k221]

kaggle

[@preetshah3793]

how to find Customer Retention & Acquisition rate or number for specific time period?

[@Ashiquei]

a great video, thank you for your hard work👏

[@GV-gn3mj]

Thank you

[@nishaagarwal6101]

So after saving the power query excel file how will I get the file for next month

[@Kiransoffice]

Is this possible to search for required sheet name when there are large number of sheets in a workbook if possible please make a video on that through your channel

[@chandoo_]

You can search for this easily. When connecting to the workbook, 1) just load any spreadsheet / named table in the navigator. 2) Now in the PQ Editor, remove the last step and go back to the "Source" step. You should see all the sheets and tables. 3) Use the filters to search for the sheet you want to load. Select only that and apply filter. 4) Use the data column to expand the data.

[@2bquest]

I have a balance sheet table that I have created using powerquery and power pivot. My problem is how I can create a total liabilities and equity row which is a sum of liabilities and equity subtotals. This is because there is no general ledger number for such a total. I hear it’s about transition context, but I don’t have that skill. Can you please do an example?

[@KumarRDAnalyst]

When we want to have to Percentage of Golds, its not mentioned the same in header, how can we understand that it is of Gold's?

[@reem_intern_doctor]

How to show the green bar (colum quality indicator) it's not shown?

[@isaackodera9441]

Beautiful content. I am ready for this Excel masterclass.

[@mirrorreflection3479]

Mistake at 28:00 Basha was the middle name...

[@hyd842]

awesome

[@ryan19899]

What to do in case of web scraping multiple pages?