Power Query can perform a lot of complex steps with any kind of data source.
However wouldn’t it be fun unless you understand what is happening under the cover.
Advanced Editor With Power Query
Let’s go through the steps in detail to understand the magic of Power Query.
Step 1: Load the data into Power Query Editor
To understand the working of Power Query, first, load the data in Power Query as shown below.
Step 2: Add Index Column
Now, make some changes to your data table. In this example, we have added an Index column to the data table.
To add Index column, go to-
After adding Index Column, the data table looks like this:-
Step 3: Rearrange the Index Column
To rearrange the Index column just drag it and place it wherever you want. Here, we have placed it to the beginning as shown in the below picture.
Step 4: Change Header Type
To change the header type of the newly added column, click on the down arrow on the column header and click on Whole Numbers option.
After changing the header type, your data table will look like this-
Step 5: View the steps applied in the Applied Steps Window
You can see the Applied Steps for adding Index Column to your data table. The steps are broken down as follows:
- SOURCE: Get the Source Table
- NAVIGATION: Navigate through all the sheets
- CHANGED TYPE: Changed the type of columns in the Table
- ADDED INDEX: Added an Index Column
- REORDERED COLUMNS: Rearrange the Index column
- CHANGED TYPE1: Change the type of Index column in the Table
Step 6: Open Advanced Editor
To open the Advanced Editor, go to-
Home > Advanced Editor
You will now see the steps in Advanced Editor, in detailed form as given in below picture.
Here, you are able to see what’s happening under the hood of your Power Query transformations.
You can learn more :
- Learn How to Delete Steps Until End in Excel
- How to Display A Monospaced Font in Excel
- Data Type Conversion With M Using Power Query
- Use Excel’s Right Function With M To Extract Left Side Data