How To Automate Data Tasks In Excel Using Power Query

Подписаться 568 тыс.

Просмотров 367 тыс.

50% 1

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

Опубликовано:

3 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 276

@avaloniangardens232 3 года назад

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!

@jenniferwoodard837 3 года назад

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 2 года назад

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.

@Leo-yz5gf 3 года назад

Thank you so much. Clear and concise, among the best tutorial i could find!

@samk.970 3 года назад

The most insightful Power Query intro. Thanks Jon for the share, keep them coming.

@keira7360 2 года назад

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

@smam60 Год назад

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!

@ajvalerio7377 3 года назад

This is the most helpful tutorial I found on the net. Very easy to follow. Thank you very much!

@esthersuh3388 Год назад

Surely Iwould become "EXCEL HERO" with your excellent instructions and videos. Thank you trillions!

@Zandor434 3 года назад

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.

@tariq313313 4 года назад

Excellent presentation. Your English Speaking is understandably clear and pleasant to non-English persons.

@christianofernandes7682 3 года назад

We are blessed with people like you

@LP-sy3md 2 года назад

Very well explained! You make everything sounds so simple!

@jchung5066 3 года назад

i follow you and ExcelIsFun! you guys are my heroes!!!

@SonofthewindsInc 3 года назад

I will be using this in ,multi data disciplines. Thank you... more power at my work place!

@hichamsouleimani1920 3 года назад

Concise demonstration. Thank you.

@benjamindale6989 3 года назад

Very concise, simple and informative. Great video!

@oldrunnerstepx2638 Год назад

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.

@wayneedmondson1065 3 года назад

Hi Jon.. excellent overview of the power of Power Query. Thanks for the video! Thumbs up!!

@raymondpang4138 4 года назад

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

@maksms Год назад

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.

@niruramananthan3524 3 года назад

You have made such a difficult Excel topic easy to understand. thank you

@markanthony7078 2 года назад

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.

@brucesun4148 4 года назад

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! 👍

@RAEN74 2 года назад

amazing as always!!! thanks for all your assistance over the years

@enochopolot333 3 года назад

Very clear and concise presentation. I'm eager to give it a go. Thanks again.

@dodettesky2587 3 года назад

Amazing tool and amazing presentation! Very easy to follow.

@peteallennh Год назад

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! 😀

@toby-tobias3651 3 года назад

Amazing video content John, learning a lot from you! Thanks

@sofianalhammouti4890 3 года назад

Your presentation is amazing!!

@edgarmachado6008 2 года назад

Very well done. Clean presentation. Succinct, clear, easy to follow, and understand.

@mypalbill 3 года назад

Excellent session and tutorial, thanks!

@guitar007 3 года назад

Thanks man, very clear presentation and very helpful !

@leoace9903 3 года назад

Thank you, you are my excel hero! 😘

@mikeschwegler3509 3 года назад

Thank you! This was great!

@marshallhosel1247 2 года назад

Useful tutorial. Thank you!

@wpfavicon6776 2 года назад

Great and amazing tutorial. John!

@jiexi144 3 года назад

Very clear explanation, easy for beginner to start with.

@SteamPunk4Life 2 года назад

Thanks, John for the awesome tutorial.

@RaviKumarCreativeCorner 2 года назад

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

@manteeful 3 года назад

Great Tutor. Amazing Presentation.

@cq1026 21 день назад

Super helpful - thank you!

@abomohnd9004 4 года назад

Short ,smart and to the point Thanks ☺️

@ExcelCampus 4 года назад

Thank you Abo! 🙌

@andyan1830 3 года назад

man, this is poweful! Great Vid!!

@abdessamedghettas7167 Год назад

Just great video many Thanks.

@livvyramsden2747 3 года назад

Great presentation. Logical steps and easy to undertstand

@GusGuitarPD 3 года назад

Awesome video. Thank you so much!

@gaetanomaximus8650 2 года назад

Awesome, thanks!

@a3akfmobile 4 года назад

Many many thanks. New and beneficiary to me.

@WeekendMuse 4 года назад

One of the best videos introducing to Excel Power Query. Well done.

@ExcelCampus 4 года назад

Glad you liked it! 😀

@Juan-Hdez Год назад

Very useful. Thank you!

@conradhaynes4672 2 года назад

An absolutely superb presentation, thank you.

@esbi1124 3 года назад

Really good and clear as usual. Thanks a lot.

@dorcasnjeri9335 2 года назад

Very, very informative. Thank you

@joielou6544 Год назад

Your videos are excellent!!!

@danielvasquez2034 4 года назад

great video! power query is a major time saver

@devenderpratap7987 3 года назад

Wonderful presentations Jon!. I learned a lot from this

@kristinamelnichenko5775 3 года назад

Awesome, thank you!

@canshocked2579 3 года назад

Very professionally presented.

@eilleensabino-laughlin7067 Год назад

This is awesome! So happy I found your page 😀

@knguyen9371 4 года назад

Thank you so much Jon. Very appreciated your knowledge sharing 👏

@ExcelCampus 4 года назад

Thanks so much K! 🙌

@orelshema911 3 года назад

Excellent Guide!

@sonjavangaale8373 3 года назад

Excellent thank you for this presentation.

@lindsaywilliams2727 4 года назад

Excellent introduction - Thanks Jon

@ExcelCampus 4 года назад

Thank you Lindsay! I appreciate your support! 🙌

@AlbrechtJ 2 года назад

Thanks for this helpful tutorial.

@abiashyt3604 3 года назад

Thanks for tutorial.Its really helpful

@michaeltobiko 2 года назад

Thank you for sharing this 🙏

@charlesmangezi9564 2 года назад

thanks, fantastic work

@fanp1188 11 месяцев назад

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 11 месяцев назад

Wow! Thank for your kind words @fanp1188 ! 😀

@sedarathnadcd 2 года назад

Thank You, It is really a important

@ScotTheGr8 2 года назад

So helpful!!!!

@SolidSnake009 3 года назад

Excellent and effective presentation. Thank you!

@ExcelCampus 3 года назад

You're very welcome! 😊

@sahiguru4913 3 года назад

Well explained and presented.

@poweruser5391 3 года назад

Really got engage with the way you explain your videos. Very organize and precise information. Kudos!

@ExcelCampus 3 года назад

Glad you like them! 😊

@devenderpratap7987 3 года назад

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

@madhavdhungel6211 2 года назад

Great, its really valuable presentation for skills enhancement. Thank you very Much

@ExcelxlNl 4 года назад

An awesome introduction tutorial and a very good explanation. Thank you!

@ExcelCampus 4 года назад

Thanks! :-)

@luciaderatmiroff1173 4 года назад

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.

@user-pb7zf8tm3x Месяц назад

excellent teaching

@HSfox 3 года назад

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

@darrylmorgan 4 года назад

Great Stuff..Thank You Jon :)

@ExcelCampus 4 года назад

Thanks Darryl! 🙏

@abdullahalnaiem3501 2 года назад

You are the best. I am naiem from Bangladesh.

@famousboyboy7426 3 года назад

Great video

@BirdsallDave 3 года назад

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?

@rahulchheda1058 4 года назад

Thank You Jon

@Mohammedshafi-Msk 4 года назад

wow! it is unbelievable

@trueaccounting3308 3 года назад

Awesome. I love your detailed expression and they are easy to learn.

@ExcelCampus 3 года назад

Glad you love them! Thank you!

@kuhekaizen3426 4 года назад

Thank you soooo much!!!!

@BenDieselBasicAutomotive Год назад

Thank you for simplifying a very complex topic.....

@ExcelCampus Год назад

You're welcome, Ben! :)

@Abbasshaha 4 года назад

excellent presentation

@sijuvarghese5469 2 года назад

Thank you.

@johnborg5419 4 года назад

Thanks jon. : )

@felipelandim2881 3 года назад

Very very interesting.

@CW-si7ib 3 года назад

very good tutorial! thank you

@ExcelCampus 3 года назад

Glad you like it! :)

@anabusso7065 3 года назад

,excellent!

@mahansor8295 3 года назад

Thanks 👍

@saifullahkowshir 3 года назад

Thank you,

@sirima14 4 года назад

Awesome, I really love this.

@ExcelCampus 4 года назад

Thank you Sirima! 🙌

@AJ-alt 3 года назад

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!

@rolo1054 4 года назад

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?