How to use Power Query as a Pivot Table Data Source?

Last Updated : 07 Mar, 2022

Power Query is a tool that is available in Microsoft Excel that simplifies the process of importing data from different sources and sorting them into an Excel sheet in the most convenient and usable format. It is a tool in Microsoft Excel that is used to simplify the process of importing data from different source files and sorting them into an Excel sheet. It also removes the need to do the same filtering techniques to transform the same data set at different points as per requirement; the user only needs to set up a query, that is, the rules for sorting, once, and refresh the query every time you need to perform a particular action. So, you can easily use a power query once and simplify your data rather than using it again and again. The biggest advantage you get when you use the Power Query is higher efficiency.

A pivot table is aggregated value such as the sum, average, minimum, and maximum of a large dataset to represent the dataset in a concise manner. Pivot tables are generally used to represent the Summary of large datasets. The input of Power Query effectively helps to put large datasets in the proper format for the pivot tables to process data.

Steps to implement Power Query as a Pivot Table datasource:

Step 1: The very first step to start with is to select the dataset for using Power Query.

Step 2: You can select From Within Sheet from Get to Transform section under Data Tab. This opens up a new screen for you where you can use Power Query to process your data in different ways to get data accordingly.

Step 3: This will open the PowerQuery window for you where you can add or delete specific rows and modify data. I would like you to explore the options available over there.

Step 4: You can change the specific group of values by selecting them. You can right-click and select replace values then you can change all specific matching values to the new value

Step 5: Now to insert multiplication for these two columns, select Quantity and Price per Quantity Column to perform operations on them. Then, select the specific operation you want to do.