Automating Your Reports With Excel's Power Query Feature

Automating Your Reports With Excel's Power Query Feature

Excel’s Power Query provides fantastic opportunities for business professionals to automate many reporting processes and practices. Ranging from simple queries to sophisticated data transformations, you can use Power Query to automatically handle many time-consuming tasks. Read on, and in this article, you will learn how you can take advantage of Power Query.

what is power query?

Power Query is an Excel tool that serves two primary purposes. First, you can use Power Query to access and link data into Excel from external data sources. These sources can be relatively simple, such as other Excel workbooks, text files, and Access databases. However, these sources can also be much more substantial, such as SQL Server databases, Oracle databases, and cloud-based data.

The second primary purpose of Power Query is to apply transformations to your data to make it more useful. These transformations can include simple tasks such as splitting and combining columns and applying filters. Additionally, these transformations can be more sophisticated, such as unpivoting data and combining data from multiple data sources.

Importantly, no matter the nature of the transformations you create with Power Query, each transformation automatically executes whenever you refresh a query. Thus, you only need to create a transformation once and Excel will automatically execute it every time you refresh the query that contains the transformation.

How Do I Obtain And Access Power Query?

Power Query first appeared as an add-in for Excel 2010. If you are running Excel 2010 or Excel 2013, click here to download the add-in. Then follow the instructions for installing the tool.  Once you install the add-in, you will see that Power Query presents its’ own tab on the Ribbon.

Fortunately, if you are running Excel 2016, Excel 2019, or Excel through an Office 365 subscription, you do not need to download the add-in. Beginning with Excel 2016, Power Query is “fully baked” into all versions of Excel, making it easier to access and use. In these newer versions of Excel, you can access all of Power Query’s functionality from the Data tab of the Ribbon as shown in Figure 1.

Accessing Power Query from the Data Tab of the Ribbon
Figure 1 - Accessing Power Query from the Data Tab of the Ribbon
An Example of Power Query Automation

To illustrate how useful Power Query can be when automating a report, assume the following scenario.

  • Each month you prepare a report that summarizes all expenditures by general ledger account.
  • To prepare this report, you receive an extraction of data from a database that summarizes the previous month’s activity.
  • You need to append the data file you receive each month to all the previous data files received for the year to provide year-to-date information in your report.
  • Presently, you spend a significant amount of time copying and pasting data and updating formulas to complete the report.

With Power Query, you can automate your report by creating a query that pulls data from all the files in a given folder to create a single data set. From this single data set, you can quickly create a PivotTable to summarize the transactions by general ledger account and by month. Then, when next month’s data becomes available, you simply place the file containing the data into the folder where the files containing the previous month’s data are located and refresh your PivotTable. At that point, your report immediately updates to include last month’s data. In other words, you can update your report by simply dragging-and-dropping the new file into the folder with the others!

Step-by-Step Instructions

The step-by-step instructions for preparing your report using this technique are as follows.

  1. Create a folder and store all the data files that will supply data to the report in that folder. The file types can be Excel, text, CSV, JSON, XML, or Access. Note that each file must be symmetrical with respect to the others. In other words, each file must have the same number of columns, the same column headers in each column, and the columns must be arranged in the same order. Further, the folder in which you store the files can only contain the files you want to incorporate into the report.
  2. From the Data tab of the Ribbon, choose Get Data and select From File, From Folder as shown in Figure 2.
Combining Multiple Files from a Single Folder
Figure 2 - Combining Multiple Files from a Single Folder

3. Choose the folder that contains the files you wish to combine for reporting purposes.

4. Next, choose Combine & Load To… to begin the process of combining the files and loading them into your report.

5. Click OK to confirm that you want to continue the process.

6. In the ensuring Import Data dialog box, choose PivotTable Report as the destination for your combined data.

7. Next, Excel will present the combined data to you in the PivotTable Field List. Create your PivotTable report by clicking-and-dragging the fields in the Field List to the desired quadrants within the PivotTable to complete the process of creating the initial report. Save the workbook containing the PivotTable report.

8. When next month’s data becomes available, simply save the file containing that data in the same folder with all previous months’ files. Then, open the workbook containing the PivotTable, right-click on the PivotTable, and click Refresh. Upon doing so, you will see that the PivotTable updates to include the newly added data.

Using Power Query, once you create the initial report, all subsequent data updates to the report will consume a few seconds of your time…now that’s real automation!

summary

Excel’s Power Query feature is one of the most noteworthy features ever added to Excel. Unfortunately, Power Query feature remains undiscovered and unused by most business professionals. As demonstrated above, this tool offers business professionals unparalleled options for linking and transforming data and automating many reporting processes. Therefore, make a commitment today to learn how Power Query can help you to advance and automate your reporting processes.

Facebook
Twitter
LinkedIn

Check Out These Courses To Learn More About Power Query