How to get values from previous step in a query without adding a new query ?

Hi Birinder ,

In Power Query, each step name is a table name. For example, if you manually created a new step by clicking the 'fx' button to the left of the formula bar, then entered ' = #"Changed Type" ' PQ would display the table exactly as it appears at that step in the transformation. You can then perform subsequent transformations on this table like you did to get your TV group, but for "Fridge" or "Washing Machine" etc.

Once you've created these subgroups within your step list, you can combine them all at the end into a single table again to be passed to the data model.

If you rename the step at the end of all your grouping transformations to a unique name, you can easily reference them all at the end.

For example:

Rename your current 'Grouped Rows' step to "groupedTVs" (try to avoid spaces and special characters in this new name as it makes it easier later).

Rename your Fridge grouping step to 'groupedFridges' and so on...

At the end, you can append them all back together again using:

Table.Combine({groupedTVs, groupedFridges, groupedWashingMachines})

Whichever table is displayed at the final step of your transformation will be the table that is passed to the data model when you hit 'Apply'.

It may be worth noting that you can multi-select (Ctrl+click) dimension columns before performing your grouping.

For example, you could multi-select [Business Unit], [Purchase], and [Method], then group and use Sum of [Payment] as your aggregated column. This would keep all of the dimension information intact, but still allow you to compare [Payment] totals.

As an aside, you may find it easier/quicker/simpler to perform this kind of aggregationin DAX from your original, ungrouped table. DAX is designed specifically for this kind of function, Power Query isn't really.

Pete