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!
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.
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 !
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!
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.
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
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!
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.
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.
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 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.
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.
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.
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!
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?
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
Hi, Jon again a good video, however, I believe the date conversion does not work the way you described it. Although it is in your local date format, it is still text. Wouldn't it be better to use "Changed Type with Locale"? I would also recommend not to remove columns. It is better to select the columns you would like to keep and use "Remove Other Columns". The source files may have gotten an additional column and this would then not removed. I learned this from another tutorial, not sure from whom though, but it makes sense to me.
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
Hi 东方不败 , thank you for your interest. The webinar will last for an hour. Here's the link to register webinars.excelcampus.com/registration-blueprint 😃
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!
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.
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
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! 👍
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!
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
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.
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
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! 😀
Surely Iwould become "EXCEL HERO" with your excellent instructions and videos. Thank you trillions!
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.
Excellent presentation. Your English Speaking is understandably clear and pleasant to non-English persons.
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.
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 ! 😀
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
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.
We are blessed with people like you
The most insightful Power Query intro. Thanks Jon for the share, keep them coming.
amazing as always!!! thanks for all your assistance over the years
You have made such a difficult Excel topic easy to understand. 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.
i follow you and ExcelIsFun! you guys are my heroes!!!
Short ,smart and to the point
Thanks ☺️
Thank you Abo! 🙌
I will be using this in ,multi data disciplines. Thank you... more power at my work place!
Thank you for simplifying a very complex topic.....
You're welcome, Ben! :)
Very concise, simple and informative. Great video!
Very well done. Clean presentation. Succinct, clear, easy to follow, and understand.
This is the most helpful tutorial I found on the net. Very easy to follow. Thank you very much!
Hi Jon.. excellent overview of the power of Power Query. Thanks for the video! Thumbs up!!
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.
Very, very informative. Thank you
Really good and clear as usual. Thanks a lot.
Very clear explanation, easy for beginner to start with.
Really got engage with the way you explain your videos. Very organize and precise information. Kudos!
Glad you like them! 😊
Great presentation. Logical steps and easy to undertstand
Great and amazing tutorial. John!
Thanks man, very clear presentation and very helpful !
Very well explained! You make everything sounds so simple!
One of the best videos introducing to Excel Power Query. Well done.
Glad you liked it! 😀
Very clear and concise presentation. I'm eager to give it a go. Thanks again.
An awesome introduction tutorial and a very good explanation. Thank you!
Thanks! :-)
Concise demonstration. Thank you.
Great, its really valuable presentation for skills enhancement. Thank you very Much
Useful tutorial. Thank you!
Great video
Thank you, you are my excel hero! 😘
Amazing tool and amazing presentation! Very easy to follow.
Excellent session and tutorial, thanks!
Very professionally presented.
An absolutely superb presentation, thank you.
Thanks for this helpful tutorial.
great video! power query is a major time saver
Your videos are excellent!!!
Wonderful presentations Jon!. I learned a lot from this
Great Tutor. Amazing Presentation.
Very useful. Thank you!
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
Excellent and effective presentation. Thank you!
You're very welcome! 😊
Many many thanks. New and beneficiary to me.
Thank You, It is really a important
Your presentation is amazing!!
You are the best. I am naiem from Bangladesh.
Thanks for tutorial.Its really helpful
Awesome. I love your detailed expression and they are easy to learn.
Glad you love them! Thank you!
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!
Thank you for sharing this 🙏
Excellent thank you for this presentation.
Thanks, John for the awesome tutorial.
Awesome, thanks!
Awesome video. Thank you so much!
This is awesome! So happy I found your page 😀
Thank you so much. Clear and concise, among the best tutorial i could find!
thanks, fantastic work
Excellent Guide!
Excellent introduction - Thanks Jon
Thank you Lindsay! I appreciate your support! 🙌
Well explained and presented.
Just great video many Thanks.
Darn, I could have saved a lot of time on friday if I watched this video earlier!
Effing same! Lil
Thank you! This was great!
man, this is poweful! Great Vid!!
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?
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
So helpful!!!!
Thank you.
excellent teaching
Thank you so much Jon. Very appreciated your knowledge sharing 👏
Thanks so much K! 🙌
excellent presentation
very good tutorial! thank you
Glad you like it! :)
,excellent!
Thank You Jon
Very very interesting.
Thanks 👍
wow! it is unbelievable
Great video!. Please share sample data to practice.
Hi, Jon again a good video, however, I believe the date conversion does not work the way you described it. Although it is in your local date format, it is still text. Wouldn't it be better to use "Changed Type with Locale"? I would also recommend not to remove columns. It is better to select the columns you would like to keep and use "Remove Other Columns". The source files may have gotten an additional column and this would then not removed. I learned this from another tutorial, not sure from whom though, but it makes sense to me.
OMG its Jamie from Progressive. Hi Jamie! :P
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
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 😃
Great Stuff..Thank You Jon :)
Thanks Darryl! 🙏
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??