Easily Import Data from Web to Excel (2 Practical Examples)
HTML-код
- Опубликовано: 6 июл 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Discover the ease of importing data from websites directly into Excel. Our tutorial demonstrates how to use Excel's 'Get and Transform' feature (aka Power Query) to seamlessly connect, transform, and load web data for analysis.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/web-query-file
✨ Key Highlights:
▪️ Simple & Dynamic Data Import: Learn how to import various types of data, such as petroleum spot prices and weather forecasts, in a few clicks.
▪️ Data Refresh with a Click: Experience the convenience of refreshing imported data with just a click, ensuring you always have the latest information.
▪️ Practical Examples: Follow two real-world examples to understand how to dynamically link Excel to web data sources for continuous updates.
▪️ Transforming Data in Excel: Discover how to clean and adjust data within Excel using Power Query, including filling down empty cells and adding emojis for enhanced visualization.
▪️ Auto-Refresh Options: Explore how to set your Excel file to automatically refresh data at regular intervals or when the file is opened.
Getting data from a web page has never been easier. Just Open Excel, go to the "Data" tab and click on "From Web". Enter the web address of the external page you'd like to import. Power Query opens and tries to find the html tables on that web page. Select the correct table and transform your data. This step gives you the ability to customize your output. You can for example, fill in the blanks by automatically copying down the top values. You can remove the columns you don't need and clean up the data. Once done, you can view the data in an Excel table, as an Excel Pivot Table or create a connection to the query.
Note: Importing web data with Power Query this way requires the tables on the web pages to be in HTML format - otherwise they're not recognized in the Navigator view.
00:00 How to Import Data from Websites to Excel
01:15 Importing Crude Oil Spot Prices to Excel
04:59 Getting the Latest Weather Data into Excel
07:25 Bonus - Using Emojis in Power Query
_____ Check out ______
Frédéric Le Guen's blog post on adding emojis to your reports: www.excel-exercise.com/add-em...
Oz's video - Emojis, Excel, Power Query & Dynamic Arrays: • Emojis, Excel, Power Q...
Check out the Power Query Playlist: • Excel Power Query (Get...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
Grab the file I used in the video from here 👉 pages.xelplus.com/web-query-file
Best part of the video is when you said; "it is just part of Excel"! That seems to be our main job as Excel Trainers, to convince Excel users that Power Query is the greatest Excel Invention since the PivotTable : )
Very well said Mike! The name power query does scare people away just like pivot tables. If pivot tables were called one-click-reports I think more people would be using it... that‘s probably why Microsoft renamed power query to
„get and transform“ when they put it in the data tab.
I find it's really inflexible like all external lookups, even to other excel files. I had lots and lots of trouble editing an existing query, for example. Ended up creating a whole new query from scratch, instead.
@@borstenpinsel You should probably check out some videos on it. It's very straight forward to use. More so with the intellisense that comes in the latest versions. When just using 2016, I do find I have to google the correct formula syntax due to lack of assistance, but that's probably a good way to learn too. Make sure you read the formula bar each time you create a step to get an understanding of the formula syntax and how you might tweak it for your queries.
@@borstenpinsel Power Query is super flexible. I don't think there's anything that it can't do.
Better than pivot tables
Thank you, Leila, I love the way you present things in such a simple and clear way!
i have been trying to learn different skills over the past few months. But all where left incomplete half way in the journey. Today started with your videos. Have been practicing them since morning. Very effective. Concise, to the point and more importantly they address real time work challenges. Thank you very much
My pleasure.
what i like about Leila is, she has a sense of art beside Excel technics.
She has what I want
... Excel knaaawledge
...and she's easy on the eye.
If the external data source requires a password you can add that in "Data Source Settings" - check out this page: support.office.com/en-us/article/data-source-settings-power-query-9f24a631-f7eb-4729-88dd-6a4921380ca9 (If you don't see the option in the Ribbon, go to Power Query, File, Options and Settings, Data Source Settings - Edit Permissions - Edit - Basic - The input username and password. )
Was wondering if you could please help me. I have Excel for Mac and there appears to be no DATA>GET DATA >FROM WEB. Mac version instead has GET EXTERNAL DATA and then RUN WEB QUERY. How do I get this done on Mac. Thanks.
Leila, you’re the total package - beautiful, brilliant, and an Excel nerd! :) 😍😍😍 Marry me?
Hi,
Is It possible to refresh based based on a cell change ? For ex if i fill cell E4 the refresh is done automatically? Thanks
I have 1000000 lakhs same data in 4 sheet how can i marge in one pivot
Thank you so much! I will try...
Leila is a combination of an artistic and techy person.. also, the best thing about her is she gives credit to whom she got the idea.
Leila, you are amazing! Thank you for open to us this precious jewel
Nice! As always, you're full of brilliance.
Question: Can you share with us some tips on how we can select a specific date such as MTD Report from the examples you've shown us?
Now that was 'Transformational' !! Great tutorial
Thank you, that was really useful. it will definitely save me a lot of time!
Great video. Quick and clean. Thanks!
Great content!Thanks so much!
THIS IS ONE OF THE BEST VIDEOS I´VE EVER SEEN ABOUT EXCEL POWER QUERY. THANKS A LOT.
Thanks so much for your videos, Leila. They have been so timely and have enabled me to make so much progress with my project.
Wow, this is super useful!
Wow! I didn’t think this was possible in Excel. Thank you.
THANKS FOR ALL YOUR WONDERFUL VIDEOS! yes, I am shouting from the rooftops about your wonderful teaching skills!
I am learning not only Excel, but how to organize a video about a concept or concrete theme, and try to explain in a short time. Really impressive Leila, you have a special touch.
I'm glad to hear that Francisco!
@@LeilaGharani This is a dream
Amazing, Thankyou for sharing your knowledge and explaining so well.
While carefully reviewing all your videos (after discovering your amazing channel), I realize about this functionality. So,... I only can say to you, Thank You Very Much. Based on it I got a bunch of ideas that will make me progress in my complex reporting.
I often say that if EXCEL would suddenly disappear, the whole world would have a deep impact at all levels.
In the same way, I want to say that your video courses, your teaching skills, and advice make the world get a bit better.
Faithfully Yours.
Thank you! I'm happy the tutorials are helpful.
Thank You... Learnt a lot
I had no idea this was possible. Thanks!
Excellent video! Thank you!
Amazing video!!
😀 happy to see this short entertaining tut, well done
Thank for the video, so interested.
Very useful...Thanks a lot.
Absolutely amazing. Never knew excel had this feature.
NOPE and i am thrilled to discover this. What a Power Tool at hand.
Thanks a lot Leila ! 🙏
That was amazing, didn't even know about that....
Like information feeding document not information breeding like make new documents? Like copying deleting paste share like that? Do those deleted files return if use special program? Emcase you wanted them back? I'll see you later on until next post? In next months?
@@brandonhowarth2402 for sure I knew that way for easily importing data from web, I wonder for the fact, that I have to give up thumb and subscribe if I like this video. That's Amazing, never knew about this option on RUclips.
Good info Leila. Keep it coming.
You are AWESOME! Thank you!
Great stuff - thanks a lot LG!
Thanks for this video
Thanks for the help! Super useful 👍
Thank you once more.
Thank you, Leila, for making things easy.
Amazing, very good. Thanks Leila!
Excatly what I needed, thank you.
Wonderful!
Thank you for this, much appreciated!
Really appreciate your teachings.
Extremely useful 👍👍👍
Nice. I learned something new. I also wanted to applaud you for giving credit for the work you borrowed from someone else. So many RUclipsrs just steal and claim content as their own
Beautifully explained, Excellent
One of my most important aspirations in 2019 is that you have a course in business intelligence tools that are as fat and complete as the VBA
Thank you for explaining this so well!
You're very welcome!
More amazing Excel majic described by Leila. What a wonderful world.
Wow. This is a very sophisticated reporting format. Thanks for teaching these kind of useful things...
You are amazing Leila! Thank you for these tips!
My pleasure Gustavo.
brilliantly explained will definitely be enrolling for the course thanks
I started to get used on your vedios, thanks thanks thanks a lot.
You are really great inspire for all content creators.
Thank you Leila!
Thank you very much
Thumbs up for the last trick.
It was amazing to use emojis in power query.
Thanks
Glad you like my little umbrella :)
YOU are great
Thanks alot
Wow..that was excellent!
How cool it is to have emoji in Power Query! That's really creative!
This is amazing! Exactly what I've been wanting to do with large budget and actual datasets. 🎉
Hi Leila. My name is Julio and I live in Brazil. I'm studying a lot about Excel. I need to give my career a boost and I believe that Excel will help me a lot.
I love your channel and the way you teach. Congratulations.
Lifesaver. Thank you!
☔☂☂ very nice video . thanks Leila
Amazing leila thanks a lot, you rock!!
Very useful ! First class tutorial
awesome! love it!
Wunderschön, Leila, das wäre genau was ich brauche. Viele liebe Grüße 🌞
Super! Freut mich :)
Thanks a lot for the video!
Always great practical knowledge for learning.
Excelent video. Thank you.
Amazing....
I was given such task before which I couldn't... You just made my day
Thank you Leila
Thanks again love it !!
Thanks for the awesome video.
this is quite helpful! saved me lots of time. thank you so much!
Am always amazed with your videos
Thank you Leila
I really appreciate your efforts
It's my pleasure
Wow! That is amazing!!! 👍🏻👍🏻👍🏻
Bravissima!!! Super clear and useful!
Amazing video! :)
Cool! Thanks Leila!
This is super. Thanks a lot Leila Gharani. I keep checking your video tutorial.
Very informative
You are Tooooo Gooood!
Thanks a lot
Thank You Ma'am
Excellent tutorial. Many thanks.
Great!
Thank you Leila. Especially like emoji part :)
I love the way you talk
You are, the BEST
Leila, You are amazing genius. :-)
Hi Liela,
Thanks for this excellent video, it makes it simple to understand how to get data form a web page.
I'm sure it will save me a lot of time.
Regards
Enrique
PD: Your English is really clear and your pronunciation makes this video understandable for everybody.
wooow it's amazing !!! I've been looking for this for a while
thank you so much Leila
My pleasure. Glad to help :)
Very enjoyable and user friendly
Very cool features. Thanks Leila!
agree - it's a really cool feature.
Absolutely amazing, thankkkkkk you Leila for this tutorial
You're very welcome.
Thank you Leila, nice and easy way to explain!!
You're very welcome 😊
Leila, eres brillante!!!!!! Muchas gracias.
Con mucho gusto Carlos :)
спасибо.ты крутая!
Hi Leila. Great tips! Love the umbrella :)) Thanks for sharing. Thumbs up!!
Haha, it's raining a lot here 😁