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
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
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
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.
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 🙏
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 ❤🙏🏻
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 ❤
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.
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
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!!
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!
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!
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
Thank you for your wonderful video. One question on data cleaning. - when we filter out null/symbol values from any column, this step will be recorded in power query. . however, upon using a new data which does not have any null values in that column, then what will happen when we refresh our loaded table ? Will it throw error because there is no null values ?
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
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.
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.
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?
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
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.
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!
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!
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?
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.
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.
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.
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"))
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?
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
I tried both the examples but I am getting an error "Collection was modified; enumeration operation may not execute". Could you please put some light on this issue.
![Felix "Unfair" | [Stray Kids : SKZ-PLAYER]](http://i.ytimg.com/vi/Oswujxm2Ag0/mqdefault.jpg)
Liked how you added gif when you run the functions, inner feelings of beginners, awesome teacher
please dont use gifs is only distracting ..though this was not that disturbing, generally I find it more childish. btw, nice explanation!
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
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
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
Please never stop making videos. We need more teachers like you.
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.
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 🙏
You are the teacher I wish I had when I was in school. Thanks for making this video enjoyable. Regards, Muhammed Mian.
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 ❤🙏🏻
So nice of you
True
Chandoo the master. I have been following your videos since I started learning Excel. You are the best instructor ever. Keep it up master
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 ❤
Mr. Chandoo! You are a Gem in the Teaching world!Thank you very much!
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.
Дуже інформативне відео. Дякую! Привіт з України.
You're such a brilliant teacher
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
So nice of you
Effectively helpful way of teaching ! Thanks ❤
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!!
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!
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!
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
I have saved your name in my head for further references...
So good in transferring information; thanks sir.
Many thanks for the Knowledge Sharing! Keep it up the good work. Cheers
May god bless you sir, your teaching is really helpful sir
Thank you sir
I really got value
❤power query
You are very good teacher sir.🙏🙏🙏🙏🙏🙏🙏🙏
Just holding your hand take me to data analysis heavens 🎉
can't wait for the next video!! I learned a lot from this video! Love the way you teach!
What a great teacher you are ! Thank you Chandoo.
Blessings, Chandoo!
Great tutorial, thank you!!!
Ma Sha Allah, very nice
Fan From TamilNadu❤
Superb explanation
Each and every point is neat and clearly understandable
Thank you very much chandoo ❤
Lots of love to you. Thank you!!!!
Excellent work!!
blow my mind, thank you so much.
Loved your work.
Thanks a lot, learning a lot from you. You're really awesome. 😎
I hope it will help me to get a first Data Analyst job. Much appreciated bro
Thank you so much for the lecture 😊
Awesome❤
Great video! What software are you using to record your screen and annotate it at the same time?
Such a good video sir, really helpful sir, thanks a lot!!!1
You are amazing, thank you very much 🙏🏻
thanku chandoo , youre really helpful
You are doing great sir
Thank you for your wonderful video.
One question on data cleaning. - when we filter out null/symbol values from any column, this step will be recorded in power query. . however, upon using a new data which does not have any null values in that column, then what will happen when we refresh our loaded table ?
Will it throw error because there is no null values ?
This is a gem
Thank you so much sir, your explanation method is very well for beginner
From Bangladesh, ❤❤❤❤❤
you are awesome brother
I LOVE YOU SIR
Very good information sir, and very neatly explained
Thank you for the tutorial.
You are great. Thank you.
Thanks a lot for providing such an informative tutorial sir🙏🏻🙏🏻
You Are Awesome...................
Excellent content..thanks for sharing 🎉
Thanks a lot sir ❤❤❤
thanks for the video
God Bless You
you are Amazing 👏🙌
Thank You Chandoo
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
Super intelligent!
Love these videos, thanks Chandoo!
Thank you sir.....
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.
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.
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
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?
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
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.
THANK YOU SIR ❤
Too good!
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!
Thank you Chan
Best 🎉
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!
Thank you ❤❤
Thank you
PLEASE SUGGEST FREELANCE JOBS IN EXCEL
You, Sir, are one of the best teachers! Thank you for sharing this lesson.
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?
You can use "Folder" query option to get data from all the files. See this example - ruclips.net/video/SGzegma9bdY/видео.html
Assomee..❤ GURU garuuu....
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.
Yes, you can. These are called "merges". See chandoo.org/wp/power-query-tutorial/ and refer to 3rd example.
commenting so i can return and revise every time someone likes
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.
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.
How can I use the name of the current file as the record in power query?
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"))
Thanks.
awesome
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!
Hi sir, can we read confidential labelled excel file in power query?
I learned power query watching your previous videos .It's really a useful tool in excel
Sir - In the name split example, please check "Nazeer Basha Mustafa" it is taking out the last name not the middle name.
a great video, thank you for your hard work👏
Hello Chandoo, Thanks for the great video...Please could you recommend any website to get data sets to practice on? Thanks
kaggle
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?
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?
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
Hai Chandoo, in my power query ,column quality indicator is not showing.
Mistake at 28:00 Basha was the middle name...
I tried both the examples but I am getting an error "Collection was modified; enumeration operation may not execute". Could you please put some light on this issue.
sir my pqe does not show coloumn quality indicator