Power Query: One-Stop Data Shopping
Thomas g. stephens, Jr., Cpa, CGMA, CITP
Like any good CPA, you want to harness the tools at your disposal to maximize your value. One oft-overlooked example is Excel’s Power Query.
Found on the Data tab of the Ribbon in the Get & Transform group, Power Query provides incredible capabilities to allow even novice users to create powerful queries that can link even complex data sets into Excel with very little effort.
Read on, and you will learn how to take advantage of this vastly underrated and underused resource.
Why Should I Use Power Query
Many business professionals routinely need to extract information from one or more data sources into Excel for summarizing, analyzing and reporting purposes. These data sources can be other Excel workbooks, text files, data on a webpage, data in an accounting or ERP database, or even data from a Facebook account. In many cases, the data contained in the source locations needs to be modified or transformed to make it useful. Moreover, oftentimes these extractions are repetitive in nature and need to be repeated on a periodic — often, monthly — basis. Absent a tool to simplify these queries, streamline the data transformations and automate the periodic refreshes of data, countless hours can be wasted on formatting the data into Excel so that it’s usable and lends itself to the summarizing, analyzing and reporting needs at hand. This is where Power Query comes in.
Specific Advantages Associated with Power Query
Power Query provides tools to assist in querying data from external data sources. In fact, Power Query currently supports querying data from 40 different sources, including other Excel files; common databases such as Access, SQL Server, SAP Hana, IBM DB2, Oracle and MySQL; text files; webpages; Facebook; Salesforce Objects and Salesforce Reports; and even Open Database Connectivity, which is a generic standard for accessing data from virtually all modern databases. This means that end-users — even those with minimal query skills — are able to use Power Query to link data from virtually any external data source into Excel. And the benefits of Power Query do not stop there.
Once you create a query with Power Query, you can use the Query Editor tool to transform the data to better meet your needs. Some examples of the many types of data transformations you can make with the Query Editor include adding and deleting columns; splitting and combining columns; transposing your data; changing data types and formats; and modifying the source of your data. What makes these transformations most useful is that Power Query automatically saves all the adjustments you make and applies them each subsequent time you run or refresh your queries. This means you’ll no longer spend countless hours reshaping your data every time you need to access it — Power Query does that for you automatically!
The last major advantage of working with Power Query is that it helps to streamline the process of refreshing and reusing queries. For example, you can set your queries to automatically update each time you open a workbook, if that is your intent. Or, you can set up a schedule to refresh the data in your workbooks every 30 minutes if that is what your needs mandate. Finally, if you have a Power BI account, you can share your queries with other team members, thereby extending the benefits of Power Query throughout the organization.
How Do I Obtain Power Query?
Power Query is fully integrated into Excel 2016 and newer. If you are running Excel 2010 or Excel 2013, you will need to download and install the free Power Query add-in from Microsoft by visiting http://www.tinyurl.com/MNquery. When downloading your add-in, be sure to select the appropriate version (32-bit or 64-bit) for your computer. Also, take a quick glance at the system requirements to verify that Power Query will run and is supported with your computer’s configuration. Remember, the add-in is free for Excel 2013 and 2010 users. Unfortunately, Power Query is not available in Excel 2007 and prior.
How Do I Work with Power Query?
Working with Power Query is quite easy, even for those users with limited database skills. From the Data tab of the Ribbon in newer versions of Excel, choose Get Data and then select your data source (if you are running the Power Query add-in in Excel 2013 or Excel 2010, you will access Power Query from the Power Query tab on the Ribbon.) If multiple tables of data exist in your data source, simply check the box next to each table you want to import into your query. Finally, click Load to query the data into the workbook’s data model or Load To for more control over the results. Figure 1 illustrates the steps outlined.
After creating the query, should you choose to edit it, open the Query Editor and make any necessary edits. In the example presented in Figure 2, the Remove Columns feature was used to delete the ETLLoadID, LoadDate and UpdateDate fields from the query because they were not deemed to be necessary. As shown near the bottom-right corner of Figure 2, Power Query automatically saved that transformation as an Applied Step; therefore, it will run each time the query is used or refreshed.
As you can see, using Power Query is quite easy!
Harness the Power!
Although Power Query has been around for nearly 10 years, most business professionals have yet to discover this amazing Excel tool and how it can be used to streamline the process of querying, transforming and updating data from external data sources.
If you find that you remain mired in antiquated and inefficient processes for getting the data you need into Excel to create your summarizations, analyses and reports, take a close look at how Power Query changes the game for linking data into Excel. When you do, you will find that Power Query does truly provide one-stop shopping for all your data.
Tommy Stephens is a shareholder in K2 Enterprises, where he develops and presents continuing professional education programs to accounting, financial and other business professionals across North America. You may reach him at tommy@k2e.com.