Power Query can let us perform a lot of complex steps with our data.

However wouldn’t it be fun if you could understand better what is happening under the hood? Come join me as we take a look into the Advanced Editor!

We will use the Index Column Post as our starting point.  Please download the Excel Workbook below.



Let’s go through the steps in detail:

STEP 1: For us to view the existing Power Query steps:

Go to Data > Get & Transform > Show Queries (Excel 2016) or

Go to Data > Get & Transform > Show Pane (Excel 2013 & 2010)

Now double click on the Table1 query.


STEP 2: This will open up the Power Query Editor.


You can see the Applied Steps we have with the Index Column workbook you downloaded above.  The steps are broken down as follows:

  • SOURCE: Get the Source Table
  • CHANGED TYPE: Changed the type of columns in the Table
  • ADDED INDEX: Added an Index Column


STEP 3: Now the fun part! Go to Home > Advanced Editor


You will now see our exact 3 steps, in detailed form!

  • Get the Table1 as the Source
  • Change the Column Type of Sales Month to text, and Sales to number
  • Add a new index column called Index that starts at 1 and increments by 1


You are now able to see what’s happening under the hood of your Power Query transformations!

Stay tuned as you join me in editing through the Power Query Advanced Editor next time!


If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn


Share on Google+


Related Posts

Inventory Your Files Using Power Query Power Query lets you perform a series of steps to transform your Excel data. One of the possible steps that surprised me was that I could list out the files in my computer!  This is useful if you want to do some analysis on your file list (i.e. finding a file, checking whic...
Consolidate Multiple Excel Sheets Using Power Quer... Power Query is awesome! You will see why after viewing this tutorial. I get lots of queries from my blog readers asking me if there is a way to easily consolidate multiple Excel worksheets into one. With Power Query the answer is YES! If you have multiple Excel worksh...
Remove Duplicates Using Power Query or Get & ... Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.  One of the steps it allows you to take is to remove duplicates easily. This removes the human error whenever you try to delete your duplicate data manually! Let...
Unpivot Data Using Excel Power Query Power Query is a free add-in created by Microsoft for Excel 2010 (or later) and you can download and install it for Excel 2010 and 2013: Click to see tutorial on how to install Power Query in Excel 2013 Click to see tutorial on how to install Power Query in Excel 2010 In...