Partners,,,, russianxnxx

If you are a Power Query ninja, you must have wanted to extract any single step output in Power Query. However, the problem is that you can only fetch the last step’s result. In this post, I’ll share with you a mind blowing technique to extract any single step of a Query.


Crazy M Tricks in Power Query – Video

The video contains three M tricks, but if you’d like to jump to the meat, skip to Trick 3 in the video.


Consider this Simple Query

I have a simple query with 6 steps.

Applied Steps

The output of the last step (“Sorted Rows”) is below,

Trick 1 Sorted

I’d like to have the ability to extract any step of the above query as a new query.

To be able to do that you’ll need to understand how do records work in Power Query.


How do Records work?

Extract any step in Power Query

  1. Records are rows of a table.
  2. Power Query stores the records along with the column headers.

Now take a look at this Query, which has a single record with three columns.

    Name = 'Chandeep',
    Country = 'India',
    Interests = {'Crazy PQ Tricks', 'Sleeping', 'Long Walks'}

Here is the result.

Creating record

A couple of things to note.

  1. Surprisingly, records appear in a columnar-like structure (but they are still rows of the table).
  2. Records are surrounded in square brackets [ ].
  3. The above query doesn’t have any let or in statement.
  4. A record value can be a single value, a list, or even a table.


Extracting any Step in Power Query

I’ll play trickery and convert my 6 step query into a record by removing the let and in statement and surrounding the entire query in square brackets. Nothing else changes.

Consider the revised M code.

    Source = Excel.Workbook(File.Contents('C:\Users\siddh\Desktop\Book1.xlsx'), null, true),
    Table1_Table = Source{[Item='Table1',Kind='Table']}[Data],
    #'Changed Type' = Table.TransformColumnTypes(Table1_Table,{{'Products', type text}, {'Units', Int64.Type}, {'Price', Int64.Type}, {'Category', type text}}),
    #'Added Custom' = Table.AddColumn(#'Changed Type', 'Sales', each [Units]*[Price]),
    #'Grouped Rows' = Table.Group(#'Added Custom', {'Category'}, {{'Total Sales', each List.Sum([Sales]), type number}}),
    #'Sorted Rows' = Table.Sort(#'Grouped Rows',{{'Total Sales', Order.Descending}})

As an output, all the steps are converted into a record.

  • The step names become the column headers
  • The value of the step is stored as a table
  • The magic is complete

Extract any step in Power Query


To put this to work, let’s create a blank query and reference it to AllSales Query (which is now a record).

Extract any step in Power Query

Now I can easily pull out any step of the query by simply clicking on the table.

Extract any step in Power Query


    ⬇️ Pop in your Name & Email to get the file!


    More on Power Query

    1. 5 Ways to Create a Date in Power Query
    2. Duplicate Rows in Power Query
    3. IFERROR In Power Query – Try And Otherwise
    4. Promote Double Headers in Power Query
    5. 5 Tricks to Reduce Steps in Power Query


    Topics that I write about...