How To Automate Data Tasks In Excel Using Power Query
Sign up for our Excel webinar, times added weekly: www.excelcampus.com/blueprint...
Learn how to use Power Query, which is a data automation tool that allows us to import, transform, and cleanse data in Excel or Power BI. In the video I share an overview of the Power Query workflow and then go into a hands on demo. You will learn how to combine multiple CSV files, clean up the data, and automate the entire process for when you get new/updated data.
This video is an introduction for beginners. If you have tried to use Power Query, but are confused by the process or user interface, then this training is for you.
Power Query is a tool that can save you a ton of time with your work by automating common data processes like:
✔ Remove columns, rows, blanks
✔ Convert data types - text, numbers, dates
✔ Split or merge columns
✔ Sort & filter columns
✔ Add calculated columns
✔ Aggregate or summarize data
✔ Find & replace text
✔ Unpivot data to use for pivot tables
✔ Merge (join) data tables together
✔ Combine (append) data tables, sheets, and workbooks
Free Webinar on The Modern Exvel Blueprint:
www.excelcampus.com/blueprint...
Additional Resources:
Read the companion article on an Overview of Power Query:
www.excelcampus.com/power-too...
Complete Guide to Installing Power Query:
www.excelcampus.com/install-p...
How to Unpivot Data with Power Query: • How to Unpivot Data wi...
How to Fix Date Errors in Power Query: • 4 Ways To Fix Your Pow...
00:00 Introduction
02:43 Demo
11:01 Updating and Refreshing
Пікірлер: 278
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!
@ExcelCampus
3 жыл бұрын
Glad you enjoyed it! Thanks! 😊
@smam60
Жыл бұрын
"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.
@debraindxb
3 жыл бұрын
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.
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
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.
Thank you so much. Clear and concise, among the best tutorial i could find!
Surely Iwould become "EXCEL HERO" with your excellent instructions and videos. Thank you trillions!
The most insightful Power Query intro. Thanks Jon for the share, keep them coming.
This is the most helpful tutorial I found on the net. Very easy to follow. Thank you very much!
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.
@ps7424
4 жыл бұрын
100% agree. really good presenter
Excellent presentation. Your English Speaking is understandably clear and pleasant to non-English persons.
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 !
@ExcelCampus
4 жыл бұрын
Thanks Bruce! Yes, great point about the evolution of Excel. I love it! 👍
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.
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!
@ExcelCampus
Жыл бұрын
Great to hear that, Pete! 😀
We are blessed with people like you
Very well explained! You make everything sounds so simple!
I will be using this in ,multi data disciplines. Thank you... more power at my work place!
Hi Jon.. excellent overview of the power of Power Query. Thanks for the video! Thumbs up!!
Very concise, simple and informative. Great video!
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.
Amazing video content John, learning a lot from you! Thanks
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.
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.
You have made such a difficult Excel topic easy to understand. thank you
Very clear and concise presentation. I'm eager to give it a go. Thanks again.
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 🎉
@ExcelCampus
Жыл бұрын
Wow! Thank for your kind words @fanp1188 ! 😀
Amazing tool and amazing presentation! Very easy to follow.
i follow you and ExcelIsFun! you guys are my heroes!!!
Very well done. Clean presentation. Succinct, clear, easy to follow, and understand.
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
Short ,smart and to the point Thanks ☺️
@ExcelCampus
4 жыл бұрын
Thank you Abo! 🙌
amazing as always!!! thanks for all your assistance over the years
Thanks man, very clear presentation and very helpful !
Concise demonstration. Thank you.
One of the best videos introducing to Excel Power Query. Well done.
@ExcelCampus
4 жыл бұрын
Glad you liked it! 😀
Thank you so much Jon. Very appreciated your knowledge sharing 👏
@ExcelCampus
4 жыл бұрын
Thanks so much K! 🙌
Wonderful presentations Jon!. I learned a lot from this
Excellent session and tutorial, thanks!
Very clear explanation, easy for beginner to start with.
Very, very informative. Thank you
Your presentation is amazing!!
Thanks, John for the awesome tutorial.
Thank you! This was great!
Really good and clear as usual. Thanks a lot.
Many many thanks. New and beneficiary to me.
Great and amazing tutorial. John!
Useful tutorial. Thank you!
Super helpful - thank you!
Great presentation. Logical steps and easy to undertstand
An awesome introduction tutorial and a very good explanation. Thank you!
@ExcelCampus
4 жыл бұрын
Thanks! :-)
Really got engage with the way you explain your videos. Very organize and precise information. Kudos!
@ExcelCampus
3 жыл бұрын
Glad you like them! 😊
man, this is poweful! Great Vid!!
Thank you, you are my excel hero! 😘
An absolutely superb presentation, thank you.
Awesome video. Thank you so much!
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.
@ExcelCampus
4 жыл бұрын
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! 🙂
@ec4ever9
4 жыл бұрын
@@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
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
Great Tutor. Amazing Presentation.
Excellent thank you for this presentation.
This is awesome! So happy I found your page 😀
Very useful. Thank you!
Excellent and effective presentation. Thank you!
@ExcelCampus
3 жыл бұрын
You're very welcome! 😊
You are the best. I am naiem from Bangladesh.
Thank You, It is really a important
Your videos are excellent!!!
Excellent introduction - Thanks Jon
@ExcelCampus
4 жыл бұрын
Thank you Lindsay! I appreciate your support! 🙌
Great, its really valuable presentation for skills enhancement. Thank you very Much
Thank you for simplifying a very complex topic.....
@ExcelCampus
Жыл бұрын
You're welcome, Ben! :)
Thanks for this helpful tutorial.
Awesome, thank you!
Excellent Guide!
excellent teaching
great video! power query is a major time saver
Just great video many Thanks.
thanks, fantastic work
Thanks for tutorial.Its really helpful
Very professionally presented.
Well explained and presented.
Awesome. I love your detailed expression and they are easy to learn.
@ExcelCampus
3 жыл бұрын
Glad you love them! Thank you!
Thank you for sharing this 🙏
Awesome, 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
Great Stuff..Thank You Jon :)
@ExcelCampus
4 жыл бұрын
Thanks Darryl! 🙏
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?
Thank You Jon
So helpful!!!!
Thank you soooo much!!!!
excellent presentation
Thank you.
very good tutorial! thank you
@ExcelCampus
4 жыл бұрын
Glad you like it! :)
wow! it is unbelievable
Thank you for the excellent intro. Really helpful. I noticed the Add Columns From example seems to require a Ctrl+Enter, but it looked like you didn’t need to do that. Is it optional?
Very very interesting.
Awesome, I really love this.
@ExcelCampus
4 жыл бұрын
Thank you Sirima! 🙌
Thanks jon. : )
Thank you,
Thanks 👍
Great video!. Please share sample data to practice.
,excellent!