Greetings, Excel enthusiasts! Welcome to another installment of Dr. Wayne Winston’s renowned Excel tips series. I'm Wayne Winston, your go-to expert for all things Microsoft Excel at Vishal. In this edition, we'll delve into the powerful realm of Excel’s exceptional tool – Power Query. Get ready to elevate your accounting and finance tasks with this indispensable Excel feature.
Business analysts often find themselves in need of a seamless method to import data from various sources, be it text files, databases, the web, or other data repositories, directly into Excel. Furthermore, these analysts typically require the ability to shape or transform this imported data while maintaining a dynamic link to the source for real-time updates. Over the next few articles, we'll explore the incredible capabilities of Power Query, a game-changer available for Excel 2016 or newer versions.
Unveiling Power Query
Let’s begin by locating Power Query. You can find it in the "Get & Transform" group on the Data Tab, as shown in Figure 1. If you're using Excel 2016 or a newer version, Power Query is a valuable addition to your data-handling toolkit.
Figure 1: Get & Transform options
Clicking on "Get Data" presents a variety of options for data sources, as depicted in Figure 2. This article will focus on importing data from the web, utilizing the "From Web" option.
Figure 2: Other Get & Transform data sources
Practical Application: Importing Population Data
For a hands-on experience, let’s import data on the largest cities in the United States, including each city's name, state, and population. We aim to have the flexibility to refresh our dataset, ensuring it automatically adapts to changes in the source website.
Step-by-Step Guide:
Access Data from the Web:
Navigate to the Data tab and choose "From Web" (or "From Other Sources" and select "From Web").
Enter the source URL, which contains information on the largest US cities' populations, into the "From Web" dialog box and click OK.
Explore Available Tables:
The Tables contained in the website are displayed. We identify that Table 2 contains the required data.
Select Table 2 and click "Transform Data" to visualize the website’s data.
Data Transformation:
A subset of the data is displayed. As we only need data from Columns 2-4, remove other columns.
Select the "2019 Rank" column, click "Remove Columns," and repeat for columns to the right of "2019 estimate."
Choose "Merge Columns" for the "City" and "State" columns, using a comma as the separator.
Final Touch:
Right-click on the first column, choose "Rename," and change it to "City and State."
Click "Close and Load" to save the transformed file.
Dynamic Data Refresh:
In the future, right-click on the transformed data, hit refresh, and Excel will automatically fetch the updated data from the source.
Power Query Unleashed
Congratulations! You've successfully harnessed the power of Power Query to import, shape, and transform data seamlessly. In the upcoming articles, we'll explore advanced applications of Power Query, including flattening Excel tables and creating tables from flat files.
Want to enhance your Excel proficiency for accounting? Enroll in Vishal’s Microsoft® Excel Fundamentals + Data Analytics Certificate, where you'll master essential Excel functions, from fundamentals to advanced applications in data analytics.
Stay tuned to the Vishal career blog for more insightful Excel tips. Elevate your Excel game and revolutionize your accounting and finance workflows with Power Query!