To use Power Query in Excel, the following steps need to be performed:

The Key Points Related to Excel Power Query.

It can be accessed from the “get data” drop-down (in the “get and transform data” group) of the Data tab of Excel. In Excel 2016 and the subsequent versions, Power Query is a built-in excel feature. Power Query can be installed as an add-in in Excel Add-in In Excel An add-in is an extension that adds more features and options to the existing Microsoft Excel. Moreover, with just a few clicks, one can have access to cleansed and sorted data. Besides, it is reusable as the same query can be used again on a new dataset. The major advantage of Power Query in excel is that it is a fast and efficient way of working on large datasets. By performing these transformations, the data is said to be shaped. All transformations (steps or changes) applied to the data are collectively known as a query. Power Query in excel performs the extract, transform, and load operations (ETL) on a dataset. It allows to select multiple data from different workbooks and consolidate it in a final workbook. This saves a lot of time and speeds up the process of consolidating excel data Consolidating Excel Data Consolidate is an inbuilt function in excel which is used to consolidate data from different workbooks which are opened at the same time. However, if pointer “b” is followed, the transformations are performed automatically every time data is updated. These tasks are often tedious and repetitive. If the organization follows the pointer “a,” it will have to perform a lot of manual work. Load the transformed data in a worksheet to create reports. Set up a query which consists of making step-by-step changes to the data.

Use Power Query to import data from the different files.

Apply the various functions of Excel to convert the data into meaningful reports.

Open the different files and copy-paste the entire data in one worksheet.

To consolidate and analyze these numbers, either of the following steps can be performed: In future, whenever the data source is updated, the same changes are performed automatically with the click of the “refresh” button.įor example, an organization has 180 files containing the purchases made in the last 15 years.

Every change made to the data is recorded and saved as a step. Power Query is an excel tool used to import data from different sources, transform (change) it as required, and return a refined dataset in the workbook.

Power query excel 2013