Extract Any Step in Power Query

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.

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

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?

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.

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

 

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

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

 

 

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