How To Automate Data Tasks In Excel Using Power Query
ฝัง
- เผยแพร่เมื่อ 1 มิ.ย. 2024
- 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
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!
Surely Iwould become "EXCEL HERO" with your excellent instructions and videos. Thank you trillions!
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 ! 😀
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!
We are blessed with people like 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.
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.
Just great video many Thanks.
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! 😀
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
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
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.
Thank you for sharing this 🙏
Very, very informative. Thank you
Thank you so much. Clear and concise, among the best tutorial i could find!
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 for simplifying a very complex topic.....
You're welcome, Ben! :)
Very well explained! You make everything sounds so simple!
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
Very concise, simple and informative. Great video!
The most insightful Power Query intro. Thanks Jon for the share, keep them coming.
Excellent presentation. Your English Speaking is understandably clear and pleasant to non-English persons.
Amazing video content John, learning a lot from you! Thanks
This is the most helpful tutorial I found on the net. Very easy to follow. Thank you very much!
Great and amazing tutorial. John!
amazing as always!!! thanks for all your assistance over the years
Wonderful presentations Jon!. I learned a lot from this
Hi Jon.. excellent overview of the power of Power Query. Thanks for the video! Thumbs up!!
You are the best. I am naiem from Bangladesh.
Thank You, It is really a important
i follow you and ExcelIsFun! you guys are my heroes!!!
I will be using this in ,multi data disciplines. Thank you... more power at my work place!
Very clear and concise presentation. I'm eager to give it a go. Thanks again.
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.
Great, its really valuable presentation for skills enhancement. Thank you very Much
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! 👍
Thanks man, very clear presentation and very helpful !
Your presentation is amazing!!
Amazing tool and amazing presentation! Very easy to follow.
Very well done. Clean presentation. Succinct, clear, easy to follow, and understand.
Concise demonstration. Thank you.
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
Really good and clear as usual. Thanks a lot.
You have made such a difficult Excel topic easy to understand. thank you
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?
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.
Useful tutorial. Thank you!
Very professionally presented.
Excellent session and tutorial, thanks!
excellent teaching
Great presentation. Logical steps and easy to undertstand
Short ,smart and to the point
Thanks ☺️
Thank you Abo! 🙌
Great video
Thanks, John for the awesome tutorial.
Very clear explanation, easy for beginner to start with.
An absolutely superb presentation, thank you.
Thanks for this helpful tutorial.
Great Tutor. Amazing Presentation.
An awesome introduction tutorial and a very good explanation. Thank you!
Thanks! :-)
Thank you so much Jon. Very appreciated your knowledge sharing 👏
Thanks so much K! 🙌
Thank you! This was great!
Many many thanks. New and beneficiary to me.
Your videos are excellent!!!
This is awesome! So happy I found your page 😀
Awesome video. Thank you so much!
man, this is poweful! Great Vid!!
Excellent thank you for this presentation.
Awesome, thanks!
One of the best videos introducing to Excel Power Query. Well done.
Glad you liked it! 😀
thanks, fantastic work
Really got engage with the way you explain your videos. Very organize and precise information. Kudos!
Glad you like them! 😊
Thank you, you are my excel hero! 😘
So helpful!!!!
Excellent introduction - Thanks Jon
Thank you Lindsay! I appreciate your support! 🙌
Excellent and effective presentation. Thank you!
You're very welcome! 😊
Very useful. Thank you!
Well explained and presented.
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 video Jon, thank you for so much information! I use Excel 2013 and the Power Query tab disappears from my spreadsheets after opening and closing Excel 5-10 times, I have to actually shut down and restart my computer to get it to show up again, what can I do to make it stop disappearing? Thank you again, this is great!
Thanks for tutorial.Its really helpful
Great overview!! I've been using Power Query for a while now but seem to have limited access to the various data sources. How is it that you have access to so many different types of data sources. I.e PDF. Are they required to be turned on from admin?
great video! power query is a major time saver
Thank you for this tutorial.. gave a good intro and helped me to start using it right away. I have a quick question. How will power query detect new columns added to source file? In this example if there is a change in division.csv and a new column is introduced, how to get power query detect? Any guidance pls
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!
Awesome presentation, Jon! Right now I manually compile and post the stats for a group of almost 50 players of an online gaming app called Golf Clash. I was wondering how, if there's a way to get their stats data from the app to my spreadsheet.
Thank you for the demo I like it. What about if I have the same source file using every day. And I want to update my daily summary table (Append) because the file contains for instance a new date every day?
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.
Excellent explanation. I really loved it watching with undivided attention. I need help in activating the power query as the editor is not appearing. Can you pl guide me on this?
Excellent Guide!
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?
Great video!. Please share sample data to practice.
You do a fantastic job presenting information. Can you use power query for excel books with multiple sheets/tabs in each one? They are set up the same in each file month over month, but want to combine all the data into the same tabular/sheet format. Thoughts? TY
Awesome, thank you!
thank you it's so useful. I've created an excel 'From Workbook" and did some tasks on it, and now I wanna change its source to "From Folder". How do I do that?
Good video! How long is the Blueprint webinar? I want to make sure I block off enough time on my calendar.
Great Stuff..Thank You Jon :)
Thanks Darryl! 🙏
Thank you so much for the lesson, how may I get the excel files to practice
Jon can you make a video about Abs and Rel, mixed references?
excellent presentation
very good tutorial! thank you
Glad you like it! :)