how to select specific rows in PowerQuery

[jona4k]

Hey there,

I would like to use the PowerQuery Language to select for each [PRODUCT_ID] the max([SELL_AMOUNT]) 

the Table Name is TABLE

Any Suggestions?

[v-shex-msft]

Hi @jona4k,

I think PhilipTreacy 's suggestion should help for your scenario.
If his formula suitable for your requirement, you can consider accept it to help other user who faced the same issue find it quickly. If not , you can post here with detailed informations.
Regards,

Xiaoxin Sheng

[PhilipTreacy · Accepted solution]

Hi @jona4k 

If you start with data like this

you can Group By and choose Max as the Operation(Aggregation)

resulting in this

Copy/paste this code into a blank query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0UIrVgTCN4SxDIzDTCMg0hbMMjRFMMzDTGMg0MoYzobqMoQpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PRODUCT_ID = _t, SELL_AMOUNT = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PRODUCT_ID", Int64.Type}, {"SELL_AMOUNT", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"PRODUCT_ID"}, {{"MAX_SELL_AMOUNT", each List.Max([SELL_AMOUNT]), type nullable number}})
in
    #"Grouped Rows"

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Anand24]

Hi @jona4k ,

You can use below DAX in a new column:
Col = CALCULATE(MAX(Table[SELL_AMOUNT]), ALLEXCEPT(Table,Table[PRODUCT_ID]))

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

[jona4k]

I know how its done in DAX, but I specifically want to do it before implementing the table. Furthermore I need only the single rows with the max(amount) from the table, but all other Columns as well So I'm basically loking for a Filter which only returns the rows including the max(amount) for each PRODUCT_ID

[PhilipTreacy]

Hi @jona4k 

This sounds like something that would be better done in DAX.  Can you please share some sample data.

Regards

Phil