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

[Birinder]

Hello, here I have a table with 4 columns.

In the Screenshot below, I filtered the table by Purchase as "Tv" and Method as "Cash"

In the screenshot below, I grouped the ID by Purchase as "Tv" and Method as "Cash" and named it as "Purchase of TV by cash"

Now my need is to use only these 2 ids as a rows for ID in my base table, and add new filters there. In more simple sense,I want to pick the top values and I want to compare them using different filters,Like Purchase as"fridge" OR "TV Purchase as Cash" for ID 1210 and 1220 by grouping. I know I can do a join, but It will take a new query to do so. Is there any way, we can do this in the same query without creatig a new query. Please help. And this is the example, I can't post the actual data because of Data confidentiality. Thank you so much In Advance.

[v-jingzhang]

Hi @Birinder 

You can try Advanced Group By for the original table. Group by "Business ID", "Purchase" and "Method" columns at the same time and aggregate the sum of Payments. In this way, you don't need to filter rows in advance to create different groups. This would be easier. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[watkinnc]

You can do:

Table.Join(Source, {"ID"}, #"Grouped Rows", {"ID"}, JoinKind.LeftOuter. 

You can refer to any step name in your current query as a table. 

--Nate

[Birinder]

Alright pete, I appreciate you help. Thank you

[Birinder]

Thank you so much for your reply. However this solution is feasible for 3-5 groups, But what If we have more than 30 group ? Writing that much larger code is still a lengthy task. 

[BA_Pete]

Hi @Birinder ,

As I mentioned at the end of my post, Power Query isn't really designed for this type of thing. It's ok to do it in small ways if you REALLY have to but, as you have noted, it's not efficient over many iterations.

DAX IS designed exactly for this, and as best practice should be used to do most grouping and aggregations on your original, unfiltered, ungrouped, data.

Pete

[BA_Pete]

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