Check out https://brightdata.grsm.io/chandoo to sign up for BrightData and to automate your data collection.
Thank you BrightData for sponsoring this video
~
Ever wanted to gather some data from web and use it for analysis? You can use Excel’s Power Query to setup and automate web scraping easily. In this tutorial, let’s look at how we can combine US state population data with Chocolate Sales data in Excel.
Sample File:
=============
Practice the steps by using the Wikipedia link:
https://en.wikipedia.org/wiki/List_of_U.S._states_and_territories_by_population
Download the sample file from here: https://chandoo.org/wp/wp-content/uploads/2022/07/web-scraping-data-to-Excel-demo.xlsx
⏱Video Topics:
=============
0:00 – Web Scraping data to Excel – The problem
1:04 – URL based data Extraction with Power Query
3:40 – Cleaning up the data (Transforming with PQ)
5:54 – Loading data to Excel
6:20 – The problems with Power Query method & solution
BRIGHTDATA:
=============
If you have a more complex data collection need, then I highly recommend using BrightData. Using their tools, you can automate data collection, clean-up and archival for all situations. If you use my link below, you get a FREE DEMO & $250 matching credit.
LINK https://brightdata.grsm.io/chandoo
~
MORE Power Query :
======================
Power Query can greatly automate & simplify your data processes. If you are new to this revolutionary technology check out below videos.
1+hr deep videos:
What is Power Query and how to use it with 4 Practical Examples (250k views) – https://youtu.be/PiFAa_jjaEI
Automate Data Tasks with Power Query (10 examples) – https://youtu.be/t43kb7BRsFY
⏳Short but powerful videos:
How to combine data from multiple sheets with PQ (250k views) – https://youtu.be/k_ugshJ4wIw
Combine data from multiple FILES with PQ (150k views) – https://youtu.be/SGzegma9bdY
Data cleaning with Excel (10 tips) – https://youtu.be/q7EpoOwBcnM
Play Lists:
Power Query tips & tricks – https://www.youtube.com/watch?v=PiFAa_jjaEI&list=PLmejDGrsgFyBqyHBq4jqdUzobCo5BDKY2
Data cleanup & automation – https://www.youtube.com/watch?v=q7EpoOwBcnM&list=PLmejDGrsgFyAjJtHYLu2iMCi1J4zT0VMh
~
#Excel #webscraping
~
A data analyst was deathly afraid of spiders. He could never web scrape.
Source
