Скачать или смотреть Excel - Refresh Data with Power Query - Episode 1914

Microsoft Excel Tutorial: Power Query is a free tool from Microsoft for Excel 2010 & Excel 2013 that lets you import imperfect data into Excel, clean it, and then later re-do the same steps with the simple click of Refresh.

Welcome to the MrExcel podcast. In this episode, we will be discussing how to refresh data with Power Query. I'm Bill Jelen and as I mentioned in our previous episode, I was at Tulsa this week for a Live Power Excel Seminar. One of the features that I demoed, which received a great response from the audience, was Power Query. This tool is part of the Microsoft Power Business Intelligence stack and is a game changer for getting imperfect data into Excel.

Let's take a look at the Power BI stack. We start with Excel, the product you already know and love. Power Query is used for getting imperfect data into Excel. The best part? It's completely free if you have Excel 2010 or 2013. You don't need any special version like Pro Plus. Today, we will be diving into the world of Power Query and how it can make your data analysis process much smoother.

To demonstrate the power of Power Query, I will be using a real-life example. I need to get data from a website that updates every minute into Excel. Normally, I would have to manually copy and paste the data and then clean it up every time I refresh. But with Power Query, I can simply copy the URL, go to Excel, and use the "From Web" option under the Power Query tab. Power Query will show me the different tables on the webpage and I can easily select the one I need. From there, I can edit the table to remove unnecessary columns and clean up the data.

The amazing thing about Power Query is that all the steps I just did are automatically recorded as program code in a new language called M. This code is stored as part of the query and can be viewed in the Advanced Editor. This means that every time I need to refresh the data, I can simply click "Refresh All" and Power Query will do all the steps for me. No more manual copying and pasting or remembering the steps I did previously.

But Power Query is not just limited to cleaning up data. It has many other powerful features, such as splitting columns and converting data types. And the best part? It's completely free for Excel 2010 and 2013 users. So why not give it a try and see how it can make your data analysis process more efficient. Thank you for tuning in to this episode of the MrExcel podcast. We'll see you next time for another netcast.

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:
(00:00) The Power BI Stack
(1:20) The data on a website
(1:40) Power Query data from web
(1:55) Choosing a Table
(2:10) Cleaning the data
(2:30) Removing "kts"
(3:10) Removing Feet and KM using custom delimiter of "feet"
(3:50) Introducing the M language
(5:10) Link to Power Map video
(5:43) Contrasting with regular Excel
(6:00) Refresh button
(6:50) Refreshing after 30 minutes
(7:10) The drudge of daily steps
(8:00) 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:
Excel 2013
Flightstats.com
Learn Excel
M language
Microsoft Power Bi stack
Microsoft Power Business Intelligence
Power Map
PowerQuery
Refreshing data

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




Note: This video also shows some Power Map, but it is a repeat of what I did in episode 1674:    • Excel - Visualize Airline Flight with 3D M...