I can't emphasize just how good a job you do presenting this information. Everything about this is perfect, the pacing, the attention to details and thoroughness, the progression, the actual use and story of it, diagrams.. Plenty I haven't touched on. Thank You so much for sharing this, you must have improved a great many lives. I'm so happy I don't have to feel the life draining out of me like so many other videos out there!
Others say it and I wholeheartedly agree - spend a few minutes in Excel Campus and you'll get the basics that are clear are easy to remember. When someone learns or retains something 'easily' it's in part down to the teacher Really knowing the subject. Thank you 🎉
Thank you for including the "why" and not just steps! It makes things easier to understand and apply in different situations. As an educator that's one of my pet-peeves, and you do a fabulous job. I wish I'd found your video first, and I look forward to seeing more videos.
Brilliant. This was perfectly presented. I have done the same thing over and over again. I never knew the refresh option works like that. I can’t wait to improve my workflow to do this. Thank you!
The 'why' was long overdue. Excellent presentation as always. A waaaaaaaay back I didn't connect with the removal of columns in the process steps as it was never explained. It took a bit and then the lights went on, lol. I don't use PQ as much as I should, more because I am so rushed in what I need to get done and have a hard time squeezing in the extra timing time and practice. However, as time marches on it becomes easier and easier to use the product. Thank you.
Awesome job consolidating all this info into fifteen minutes! If someone is experienced with Excel they really don't need a three hour introduction to understand what Power Query is. Too much information is overwhelming, which keeps us from even trying. After watching this I finally "get it" and am ready to try this the next time I clean up data. Thank you so much!
Omg can’t believe I finally found you I’ve watched 5 vids so far and everything is just so extraordinary, super simple to understand. You’re the best instructor I’ve ever seen in my 29yrs of living
Having seen videos on PowerQuery presented by other people, I have learned a lot from you, as I like the way you organized & presented your material, which made it easier for me to understand.
Thanks for sharing! Excel has evolved into a such powerful data tool ! It’s basically becoming an agile version of a mixture of power BI, Access, and traditional excel !
This was such an extraordinary video which made me understand the very basics of power query. I also appreciate your communication which is crystal clear, no repetitions and great with your subject matter. More than everything I really appreciate creating such educational videos which help people like us to get knowledge free of cost. Thank you so much.
Jon, this video is a crystal-clear explanation and demonstration of a very powerful Excel feature. Very impressed by the quality of this video, have now subscribed as the Power Pivot and Pivot Tables parts look interesting too. I already use Pivot Tables but I'm sure your video will fill in any gaps on my knowledge.
Thank you soo soo much Jon for this free webinar and lectures you do. You are Amazing and I cannot wait to enroll on one of your programs fairy soon. Your teaching is to the point. This video really helped me on a difficult rushed project today. See you on one of your courses very soon, From UK.
This is great. i watched hours of videos on this topic months ago and battled through many of the ins and outs you described. Your video summarizes the business reasons behind this type of analysis so well. Thank you for taking the time to present this in such a digestible manner. I'll be taking your webinar at the first opportunity.
I love your videos. Explanations are understandable and to the point. I am an older follower, do a lot of stats for non-profits as volunteer, and I work on Mac OS. Sometimes it is difficult to apply your videos, but I will try with this one because it will save me tons of time! Thanks.
Thanks Lucia! I appreciate your support. In regards to Power Query on the Mac, it is not fully available yet for the Mac version of Excel. However, Microsoft is working on making it available and currently in phase 1 of the project. This means you can only refresh queries on the Mac. You cannot create or edit them yet. Here is an article that explains more (techcommunity.microsoft.com/t5/excel-blog/get-amp-transform-power-query-in-excel-for-mac-phase-1/ba-p/876840) One workaround is to install a virtualization app like Parallels (www.parallels.com/) on your Mac. This will allow you to run Windows on your Mac and install the Windows version of Excel. If you have an Office 365 subscription then the Windows version of Office will just count as one of your users. You will then be able to use all "power" tools including Power Query, Power Pivot, Power BI and full VBA. I hope that helps. Thanks again and have a nice day! 🙂
@@ExcelCampus Thanks for the question Lucia and thanks for your response Jon. I've been sitting on my Mac for days trying to figure out how to Get & Transform and apparently it's not available yet. You both saved me some time and maybe a couple headaches.
Nice video, thanks! So, I'm a long-time SQL user, but I am trying to put together an Excel spreadsheet without an SQL database behind it for some folks who don't have access to an SQL database. I have a few simple tables that I want to join together, and use like an SQL materialized view. I've played with putting each table in a sheet, then using Power Query to join them in a new sheet. This works fairly well. And I find that indeed if I change data in the source tables and hit "Refresh All", the joined data updates nicely. Next thing I tried was saving the Excel workbook, exiting Excel, then opening the workbook again. Then I wanted to repeat the exercise: update one of the source tables, then hit "Refresh All" to see the joined data automagically updated. This doesn't work, though; Excel complains about connections. So here's my question: Can one make re-establishing connections automatic, so that there aren't any extra steps when reopening the workbook?
We can also right click on date column and select change type and select using locale.. select date from drop-down and change to us or your country type no need to add column and again delete date column btw i got to know the alternative.. l like this
Great video! What Id like to know is when you close the sheet where you merge the files, where does it go or is it something that you can save as an Excel sheet? 2. You show how to combine the results in one sheet, what if I want the Applied Steps to be applied on individual sheet because the report should be separate instead of merge reports. This is for my daily and weekly reports. Thanks!
Hi 东方不败 , thank you for your interest. The webinar will last for an hour. Here's the link to register webinars.excelcampus.com/registration-blueprint 😃
Thanks for the vote Abid! I am working on more Power Query videos and have one coming out this week. We also have in-depth training on Power Query in our Elevate Excel Training Program (excelcampus.com/elevate). I hope that helps. Thanks again and have a nice day! 🙂
This put me into this wonderful concept during the lockdown time where we hardly had any software loaded in our laptop. This is awesome and magic bullet. But, tell me weather it can handle calculated columns
This is a brilliant tutorial, thank you very much! Despite your clear explanations, I'm still experiencing endless problems with the Date format. Whenever I get CSV data with date fields into Power Queary, and then try to convert that to Date format, it reverts to the US mm/dd/yyyy format. I need it to be dd/mm/yyyy format. I've checked Regional settigns in the Control Panel, the locale settings in Power Queary, but nothing works. Even the Column from Example technique does not work. Please help?
What about inconsistent column headers ?? For example- we have a tool from which we can pull data but sometimes we add new columns so let’s say “last name” was column B - but the following download you’ll find it in column C- do you have to manually clean up the spreadsheets before combining??
I can't emphasize just how good a job you do presenting this information. Everything about this is perfect, the pacing, the attention to details and thoroughness, the progression, the actual use and story of it, diagrams.. Plenty I haven't touched on. Thank You so much for sharing this, you must have improved a great many lives. I'm so happy I don't have to feel the life draining out of me like so many other videos out there!
Glad you enjoyed it! Thanks! 😊
"feel the life draining out of me!" 😁👍. Yep-same here but you described it perfectly!
You have an excellent way of explaining /training. I’ve learned so much in the first two videos I watched today and can’t wait to put them in practice
Others say it and I wholeheartedly agree - spend a few minutes in Excel Campus and you'll get the basics that are clear are easy to remember. When someone learns or retains something 'easily' it's in part down to the teacher Really knowing the subject. Thank you 🎉
Wow! Thank for your kind words @fanp1188 ! 😀
Thank you for including the "why" and not just steps! It makes things easier to understand and apply in different situations. As an educator that's one of my pet-peeves, and you do a fabulous job. I wish I'd found your video first, and I look forward to seeing more videos.
Ditto. I am a data analyst with a master's degree in Education. Jon has hit all the important instructional bits to make his instruction successful.
Brilliant. This was perfectly presented. I have done the same thing over and over again. I never knew the refresh option works like that. I can’t wait to improve my workflow to do this. Thank you!
Great to hear that, Pete! 😀
The 'why' was long overdue. Excellent presentation as always. A waaaaaaaay back I didn't connect with the removal of columns in the process steps as it was never explained. It took a bit and then the lights went on, lol. I don't use PQ as much as I should, more because I am so rushed in what I need to get done and have a hard time squeezing in the extra timing time and practice. However, as time marches on it becomes easier and easier to use the product. Thank you.
Awesome job consolidating all this info into fifteen minutes! If someone is experienced with Excel they really don't need a three hour introduction to understand what Power Query is. Too much information is overwhelming, which keeps us from even trying. After watching this I finally "get it" and am ready to try this the next time I clean up data. Thank you so much!
Omg can’t believe I finally found you
I’ve watched 5 vids so far and everything is just so extraordinary, super simple to understand. You’re the best instructor I’ve ever seen in my 29yrs of living
Having seen videos on PowerQuery presented by other people, I have learned a lot from you, as I like the way you organized & presented your material, which made it easier for me to understand.
100% agree. really good presenter
Surely Iwould become "EXCEL HERO" with your excellent instructions and videos. Thank you trillions!
Thanks for sharing! Excel has evolved into a such powerful data tool ! It’s basically becoming an agile version of a mixture of power BI, Access, and traditional excel !
Thanks Bruce! Yes, great point about the evolution of Excel. I love it! 👍
Excellent presentation. Your English Speaking is understandably clear and pleasant to non-English persons.
This was such an extraordinary video which made me understand the very basics of power query. I also appreciate your communication which is crystal clear, no repetitions and great with your subject matter.
More than everything I really appreciate creating such educational videos which help people like us to get knowledge free of cost.
Thank you so much.
wow, excellent. i used think that it is very hard to learn excel but after watching your videos, i have learned so much. Thank you
Jon, this video is a crystal-clear explanation and demonstration of a very powerful Excel feature. Very impressed by the quality of this video, have now subscribed as the Power Pivot and Pivot Tables parts look interesting too. I already use Pivot Tables but I'm sure your video will fill in any gaps on my knowledge.
We are blessed with people like you
You have made such a difficult Excel topic easy to understand. thank you
Thank you for simplifying a very complex topic.....
You're welcome, Ben! :)
amazing as always!!! thanks for all your assistance over the years
Thank you soo soo much Jon for this free webinar and lectures you do. You are Amazing and I cannot wait to enroll on one of your programs fairy soon. Your teaching is to the point. This video really helped me on a difficult rushed project today. See you on one of your courses very soon, From UK.
This is great. i watched hours of videos on this topic months ago and battled through many of the ins and outs you described. Your video summarizes the business reasons behind this type of analysis so well. Thank you for taking the time to present this in such a digestible manner. I'll be taking your webinar at the first opportunity.
i follow you and ExcelIsFun! you guys are my heroes!!!
The most insightful Power Query intro. Thanks Jon for the share, keep them coming.
Really got engage with the way you explain your videos. Very organize and precise information. Kudos!
Glad you like them! 😊
Very, very informative. Thank you
Short ,smart and to the point
Thanks ☺️
Thank you Abo! 🙌
Great and amazing tutorial. John!
Great, its really valuable presentation for skills enhancement. Thank you very Much
Very clear explanation, easy for beginner to start with.
Super helpful - thank you!
Great presentation. Logical steps and easy to undertstand
Very well done. Clean presentation. Succinct, clear, easy to follow, and understand.
One of the best videos introducing to Excel Power Query. Well done.
Glad you liked it! 😀
Thank You, It is really a important
Useful tutorial. Thank you!
You are the best. I am naiem from Bangladesh.
Very professionally presented.
Really good and clear as usual. Thanks a lot.
I love your videos. Explanations are understandable and to the point. I am an older follower, do a lot of stats for non-profits as volunteer, and I work on Mac OS. Sometimes it is difficult to apply your videos, but I will try with this one because it will save me tons of time! Thanks.
Thanks Lucia! I appreciate your support. In regards to Power Query on the Mac, it is not fully available yet for the Mac version of Excel. However, Microsoft is working on making it available and currently in phase 1 of the project. This means you can only refresh queries on the Mac. You cannot create or edit them yet. Here is an article that explains more (techcommunity.microsoft.com/t5/excel-blog/get-amp-transform-power-query-in-excel-for-mac-phase-1/ba-p/876840)
One workaround is to install a virtualization app like Parallels (www.parallels.com/) on your Mac. This will allow you to run Windows on your Mac and install the Windows version of Excel. If you have an Office 365 subscription then the Windows version of Office will just count as one of your users. You will then be able to use all "power" tools including Power Query, Power Pivot, Power BI and full VBA.
I hope that helps. Thanks again and have a nice day! 🙂
@@ExcelCampus Thanks for the question Lucia and thanks for your response Jon. I've been sitting on my Mac for days trying to figure out how to Get & Transform and apparently it's not available yet. You both saved me some time and maybe a couple headaches.
thanks, fantastic work
An absolutely superb presentation, thank you.
Great video
Thanks for this helpful tutorial.
Concise demonstration. Thank you.
excellent teaching
Excellent session and tutorial, thanks!
This is great!! Thanks :D I feel like I've spent soo much time cleaning data and never cared for power query :( I was dumb to think that
Thanks man, very clear presentation and very helpful !
Hi Jon.. excellent overview of the power of Power Query. Thanks for the video! Thumbs up!!
Very concise, simple and informative. Great video!
Your videos are excellent!!!
Awesome, thanks!
Nice video, thanks! So, I'm a long-time SQL user, but I am trying to put together an Excel spreadsheet without an SQL database behind it for some folks who don't have access to an SQL database. I have a few simple tables that I want to join together, and use like an SQL materialized view. I've played with putting each table in a sheet, then using Power Query to join them in a new sheet. This works fairly well. And I find that indeed if I change data in the source tables and hit "Refresh All", the joined data updates nicely. Next thing I tried was saving the Excel workbook, exiting Excel, then opening the workbook again. Then I wanted to repeat the exercise: update one of the source tables, then hit "Refresh All" to see the joined data automagically updated. This doesn't work, though; Excel complains about connections. So here's my question: Can one make re-establishing connections automatic, so that there aren't any extra steps when reopening the workbook?
Excellent and effective presentation. Thank you!
You're very welcome! 😊
great video! power query is a major time saver
Amazing tool and amazing presentation! Very easy to follow.
Great Tutor. Amazing Presentation.
Very clear and concise presentation. I'm eager to give it a go. Thanks again.
This is the most helpful tutorial I found on the net. Very easy to follow. Thank you very much!
Thank you for sharing this 🙏
Thank you, you are my excel hero! 😘
Thanks for tutorial.Its really helpful
I will be using this in ,multi data disciplines. Thank you... more power at my work place!
Awesome. I love your detailed expression and they are easy to learn.
Glad you love them! Thank you!
Excellent thank you for this presentation.
Very well explained! You make everything sounds so simple!
Excellent introduction - Thanks Jon
Thank you Lindsay! I appreciate your support! 🙌
An awesome introduction tutorial and a very good explanation. Thank you!
Thanks! :-)
This is awesome! So happy I found your page 😀
Thank you.
Darn, I could have saved a lot of time on friday if I watched this video earlier!
Effing same! Lil
Many many thanks. New and beneficiary to me.
Just great video many Thanks.
Great video!. Please share sample data to practice.
Thanks, John for the awesome tutorial.
Awesome video. Thank you so much!
Excellent Guide!
Well explained and presented.
Your presentation is amazing!!
We can also right click on date column and select change type and select using locale.. select date from drop-down and change to us or your country type no need to add column and again delete date column btw i got to know the alternative.. l like this
Great video! What Id like to know is when you close the sheet where you merge the files, where does it go or is it something that you can save as an Excel sheet? 2. You show how to combine the results in one sheet, what if I want the Applied Steps to be applied on individual sheet because the report should be separate instead of merge reports. This is for my daily and weekly reports. Thanks!
Amazing video content John, learning a lot from you! Thanks
excellent presentation
very good tutorial! thank you
Glad you like it! :)
Thank you so much Jon. I would like to register your free webinar. I wonder how many hours the training will be? Thanks again.
Hi 东方不败 , thank you for your interest. The webinar will last for an hour. Here's the link to register webinars.excelcampus.com/registration-blueprint 😃
8:29 you could just change type of the column with 'locale' and then follow with another step with 'date'
Thanks 👍
Hello jon, please make full series on power query. Thanks
Thanks for the vote Abid! I am working on more Power Query videos and have one coming out this week. We also have in-depth training on Power Query in our Elevate Excel Training Program (excelcampus.com/elevate).
I hope that helps. Thanks again and have a nice day! 🙂
Can you make a Video on how to auto update data from any data source to a designated folder in power query? Many thanks!
This put me into this wonderful concept during the lockdown time where we hardly had any software loaded in our laptop. This is awesome and magic bullet. But, tell me weather it can handle calculated columns
,excellent!
This is a brilliant tutorial, thank you very much! Despite your clear explanations, I'm still experiencing endless problems with the Date format. Whenever I get CSV data with date fields into Power Queary, and then try to convert that to Date format, it reverts to the US mm/dd/yyyy format. I need it to be dd/mm/yyyy format. I've checked Regional settigns in the Control Panel, the locale settings in Power Queary, but nothing works. Even the Column from Example technique does not work. Please help?
As an IT Student we dont use Excel a lot instead we use SQL, but this was informative.
Thank you so much. Clear and concise, among the best tutorial i could find!
Thank you! This was great!
Very very interesting.
What about inconsistent column headers ?? For example- we have a tool from which we can pull data but sometimes we add new columns so let’s say “last name” was column B - but the following download you’ll find it in column C- do you have to manually clean up the spreadsheets before combining??
man, this is poweful! Great Vid!!
Thank You Jon
wow! it is unbelievable