Скачать или смотреть Excel - Make Ugly Web Data Beautiful With Power Query - Episode 1947

Microsoft Excel Tutorial: Cleaning web data in Power Query.

Welcome to the MrExcel podcast. In this episode, we will be discussing how to use Power Query to transform ugly web data into a beautiful and usable format in Excel. As we all know, working with web data can be a frustrating and time-consuming task, but with the help of Power Query, we can easily extract and transform the data we need.

In this tutorial, I will be using a project where I needed to create fake data with last names. After a quick Google search, I stumbled upon a website called mongabay.com that had a long list of surnames. However, when I copied and pasted the data into Excel, I noticed that the last column was separated from the rest of the data, making it difficult to use. This is where Power Query comes in.

Power Query is a free tool from Microsoft that is available for Excel 2010 and higher versions. It allows us to easily extract data from the web and transform it into a usable format. In this case, I simply copied the URL of the website and used Power Query to retrieve the data. After a few seconds, Power Query was able to analyze the website and extract the data in a table format.

Once the data was loaded into Excel, I noticed that there were some unnecessary columns that I didn't need. With the help of Power Query, I was able to easily remove these columns and keep only the data I needed. This saved me a lot of time and effort compared to manually editing the data. With Power Query, we can also refresh the data with just a click, making it easy to keep our data up to date.

In conclusion, Power Query is a powerful tool that can help us transform ugly web data into a beautiful and usable format in Excel. It is a must-have for anyone working with web data and can save you a lot of time and frustration. So next time you encounter a website with difficult-to-use data, remember to use Power Query to make your life easier. Thanks for watching and don't forget to subscribe to our channel for more Excel tips and tricks. See you next time on the MrExcel podcast!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...


Table of Contents:
(0:00) Problem Statement: Ugly web data
(0:30) Copy from web and paste to Excel
(1:16) Copy URL
(1:34) Excel Power Query from Web
(1:55) Preview of tables found
(2:15) Remove other columns
(03:01) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
Data analysis
Excel Copy and Paste
Fake data
Google search
Last name
Learn Excel
Microsoft
Power Query
Power Query tool
Surnames

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...

After trying to scrape some data from a web table into Excel, some modern web trickery is causing the data to copy to the clipboard in a way that all the data pastes into a single cell in Excel. Power Query comes to the rescue, pulling the data correctly into Excel.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/