Easily Import Data from Web to Excel (2 Practical Examples)
Vložit
- čas přidán 2. 06. 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
Thank you, Leila, I love the way you present things in such a simple and clear way!
This is amazing! Exactly what I've been wanting to do with large budget and actual datasets. 🎉
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.
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?
Thank you, that was really useful. it will definitely save me a lot of time!
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
Amazing, Thankyou for sharing your knowledge and explaining so well.
Great video. Quick and clean. Thanks!
Thanks so much for your videos, Leila. They have been so timely and have enabled me to make so much progress with my project.
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.
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.
THANKS FOR ALL YOUR WONDERFUL VIDEOS! yes, I am shouting from the rooftops about your wonderful teaching skills!
THIS IS ONE OF THE BEST VIDEOS I´VE EVER SEEN ABOUT EXCEL POWER QUERY. THANKS A LOT.
Now that was 'Transformational' !! Great tutorial
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.
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
More amazing Excel majic described by Leila. What a wonderful world.
Thank you, Leila, for making things easy.
Nice. I learned something new. I also wanted to applaud you for giving credit for the work you borrowed from someone else. So many CZcamsrs just steal and claim content as their own
Thank you for explaining this so well!
You're very welcome!
Amazing....
I was given such task before which I couldn't... You just made my day
Thank you Leila
Wow! I didn’t think this was possible in Excel. Thank you.
Wow, this is super useful!
Absolutely amazing. Never knew excel had this feature.
NOPE and i am thrilled to discover this. What a Power Tool at hand.
Wow. This is a very sophisticated reporting format. Thanks for teaching these kind of useful things...
Great content!Thanks so much!
Mind blowing...! Saving money to buy your black belt course and add value to my life.
😀 happy to see this short entertaining tut, well done
brilliantly explained will definitely be enrolling for the course thanks
Thanks for the help! Super useful 👍
You are amazing Leila! Thank you for these tips!
My pleasure Gustavo.
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.
You are AWESOME! Thank you!
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
Beautifully presented.
Is there a technique for dealing with multiple pages within a page-ie those buttons you click '2', '3', 'Next'-so that Refresh will pull them all in?
My current need has a variable number of pages, some days 10, some days 15…
I started to get used on your vedios, thanks thanks thanks a lot.
You are really great inspire for all content creators.
Beautifully explained, Excellent
Really appreciate your teachings.
Amazing, very good. Thanks Leila!
Am always amazed with your videos
Thank you Leila
Always great practical knowledge for learning.
Excellent video! Thank you!
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 CZcams.
Thank You... Learnt a lot
Good info Leila. Keep it coming.
Thumbs up for the last trick.
It was amazing to use emojis in power query.
Thanks
Glad you like my little umbrella :)
Thank you for this, much appreciated!
this is quite helpful! saved me lots of time. thank you so much!
This is super. Thanks a lot Leila Gharani. I keep checking your video tutorial.
Excelent video. Thank you.
Thank for the video, so interested.
Very useful...Thanks a lot.
I had no idea this was possible. Thanks!
I am a regular watcher of technology videos but the you explain things is just awesome thank you so much your initiative
Excellent tutorial. Many thanks.
Thank you Leila, nice and easy way to explain!!
You're very welcome 😊
That was very helpful. Always bee looking to start with the power query, I feel your explanation made it look like it's easy so now I will give it a try.
Keep inspiring :)
Thanks for the awesome video.
Hi Leila,
Thanks for the really helpful video. I'm just curious if there's a way you can create a specific "template" for power query when you edit a web page data source. For example, I download the same tables weekly from a certain site and edit them the same way (I have to do this because the exact url changes). So essentially, can you create a template for transforming the data where the same processing of splitting columns etc. is performed? Thanks heaps.
Kind regards,
Ethan
wooow it's amazing !!! I've been looking for this for a while
thank you so much Leila
My pleasure. Glad to help :)
How cool it is to have emoji in Power Query! That's really creative!
Wunderschön, Leila, das wäre genau was ich brauche. Viele liebe Grüße 🌞
Super! Freut mich :)
Amazing video!!
Amazing leila thanks a lot, you rock!!
Very useful ! First class tutorial
Power Query is awesome. Thanks Leila..
It sure is! Thank you for your support Matt.
Hi Leila. Great tips! Love the umbrella :)) Thanks for sharing. Thumbs up!!
Haha, it's raining a lot here 😁
I opened the Emog (Window Key + the period) in the area where you pasted the umbrella Equipped and selected all the different symbols I wanted. Then Cut and pasted them in the other cells. Thanls for the fun weather program.
Great stuff - thanks a lot LG!
Absolutely amazing, thankkkkkk you Leila for this tutorial
You're very welcome.
Very cool features. Thanks Leila!
agree - it's a really cool feature.
Excatly what I needed, thank you.
Leila, eres brillante!!!!!! Muchas gracias.
Con mucho gusto Carlos :)
Lifesaver. Thank you!
Predictive Excel so easy with YOU 🌟
Thanks for this video
you are really genius EXCEL world no.1
Hi Leila, Great videos and lessons all very much appreciated. I am also getting troubles with tables not showing and on some sites "table highlighting is disabled because the page uses the Internet Explorer's Compatibility Mode", do you have a suggestion on a workaround for this
Bravissima!!! Super clear and useful!
Ola Leila. Adoro os seus videos. Estou seguindo e posso lhe dizer que ja aprendi bastante e tenho usado alguns dos ensinamentos aqui no meu trabalho. Bom trabalho. Abracos.
I'm glad the videos are helpful Fernando.
Thank you once more.
Leila, amazing video, super well explained, thank you/muito obrigado!
My pleasure, Paulo!
Thanks a lot for the nice explanation. just one question: if I import the data from a table with links, they are loaded into excel as text. Is there a method to also import the hyperlink format??
I love the way you talk
Extremely useful 👍👍👍
Hi Leila, thanks for this. However, I was curious, how we can refresh data to be added to a different column at frequent intervals, an example would be for stock price movements over time and add a chart to that.
Thank you Leila. Especially like emoji part :)
You are realy great. You saved my day and job. Thanks a lot.
Thanks a lot for the video!
Leila you always amazed us with your versatile brain 🧠. You beauty with the brain.
That was literally phenomenal... Excel is really a power tool for entrepreneurs such as myself.
👍
this is Brilliant!! I had no clue that so much power was avail in Excel for web interfacing.
*THANK YOU* *THANK YOU* *(NEW SUB)*
Thanks for the sub!
Sheer presentation wins my ❤️
I really appreciate your efforts
It's my pleasure
Nice clear and concise explanation. I real like your videos.
سپاسگزارم آموزش بسیار عالی لود. ،🌺
Thank you very much