Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Birinder
Helper III
Helper III

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

Hello, here I have a table with 4 columns.

Screenshot (6).png

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

Screenshot (7).png

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"

Screenshot (8).png

 

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.

6 REPLIES 6
v-jingzhang
Community Support
Community Support

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. 

21100506.jpg

 

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

watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
Birinder
Helper III
Helper III

Alright pete, I appreciate you help. Thank you

Birinder
Helper III
Helper III

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. 

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors