From this single data set, you can quickly create a PivotTable to summarize the transactions by general ledger account and by month. 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.

Presently, you spend a significant amount of time copying and pasting data and updating formulas to complete the report.

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.

To prepare this report, you receive an extraction of data from a database that summarizes the previous month’s activity.

Each month you prepare a report that summarizes all expenditures by general ledger account.

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

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. Importantly, no matter the nature of the transformations you create with Power Query, each transformation automatically executes whenever you refresh a query.

Additionally, these transformations can be more sophisticated, such as unpivoting data and combining data from multiple data sources. These transformations can include simple tasks such as splitting and combining columns and applying filters. The second primary purpose of Power Query is to apply transformations to your data to make it more useful. However, these sources can also be much more substantial, such as SQL Server databases, Oracle databases, and cloud-based data. These sources can be relatively simple, such as other Excel workbooks, text files, and Access databases. First, you can use Power Query to access and link data into Excel from external data sources. Power Query is an Excel tool that serves two primary purposes.

Open power query in excel