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.
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.
- 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.
- From the Data tab of the Ribbon, choose Get Data and select From File, From Folder as shown in Figure 2.
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.
Check Out These Courses To Learn More About Power Query
Leave those manual reporting processes behind! Instead, move into the new world of automatically linking data into Excel for analyzing, reporting, and preparing visualizations. Using tools such as Power Query and Power Pivot, you can perform Excel Data Magic! In this environment, you can leverage your knowledge of PivotTables to do even more when it comes to summarizing, analyzing, and reporting.
In Excel Data Magic, you will learn how to create queries to link data from external databases into Excel. From there, you will learn transform this data into Data Models, the new cornerstone for reporting and analytics. Additionally, you will learn how to use Power Pivot and CUBE formulas to streamline the process of summarizing and reporting this data. You will also learn how to take advantage of advanced features such as Data Analysis Expressions, Key Performance Indicators, Hierarchies, and Dimensions to streamline your reporting processes. Further, you will learn how to create compelling visualizations of this summarized data to help your readers understand the true meaning behind the numbers. If you currently use PivotTables on a regular basis and want to take advantage of the powerful advanced reporting features available in Excel, then sign up today for this program.
Note: Excel Data Magic is taught at an advanced level; other, more fundamental coverage of PivotTables is offered in K2’s Excel PivotTables for Accountants seminar. Many of the concepts taught in this course depend upon access to Power Pivot, Power Query, and Power View, available only in the Windows-based premium editions of Excel 2013 and newer, including versions provided through Office 365 subscriptions.
CLICK HERE for more information on this course.
If you are an experienced Excel user seeking to elevate your skills, join the K2 team for K2’s Advanced Excel. This session draws from over twenty-five years of experience in delivering Excel training to accountants and other business professionals. As a result, you will find the content of this session to be both timely and relevant.
By participating in this session, you will learn critical advanced Excel skills in six key areas.
1) How to collaborate with others and secure your workbooks
2) Using tables to analyze and report data
3) Working with data from external sources
4) The best ways to create and audit complex formulas
5) Advanced data analysis tools
6) Visualization techniques to analyze and communicate information
Additionally, throughout the program, relevant, real-world examples are used to reinforce the major topics presented. Therefore, you should make plans now to attend K2’s Advanced Excel for the opportunity to become more efficient and proficient in Excel. Notably, this course covers advanced Excel topics found in Excel 2007 and newer.
CLICK HERE for more information on this course.