Create top 2 table per category with sku

[arutsjak90]

HI, I am trying to create new table with top2 values per category. 

Category	SKU	QTy
A	1	10
A	2	5
A	3	15
A	4	2
B	5	6
B	6	7
B	7	9
C	8	12
C	9	7

Expected result (new table)

Category	SKU	QTy
A	3	15
A	1	10
B	7	9
B	6	7
C	8	12
C	7	9

Is it possible to create in Power BI, it is important for me to have new table.

Thank you in advance!

[Vera_33]

Hi @arutsjak90 

How do you like, DAX calculated table or Power Query? Here is a way in M, paste in Advanced Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYQOlWB0I1wiITeE8Y5AkgmsCVgDiOYGV6SiZwXlmQGwO55kDsSWY5wxkWYCMMYJzLSFKYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, SKU = _t, QTy = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"SKU", Int64.Type}, {"QTy", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Category", Order.Ascending}, {"QTy", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Category"}, {{"allrows", each _, type table [Category=nullable text, SKU=nullable number, QTy=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.FirstN([allrows],2)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Category", "SKU", "QTy"}, {"Category", "SKU", "QTy"})
in
    #"Expanded Custom"

[arutsjak90]

HI Vera_33, thank you for your hard work! 😊 I'am not sure and I didn't mention that but if my current table is a calculated table (summarize) then I can not use it in M? If so, dax is a only way, ofc in my beginner opinion. 

[arutsjak90 · Accepted solution]

OK, resolved. I have created new column using rankx, then I have created new table using CALCULATE TABLE (SUMMARIZE(), RANK <3). Thank you for your help.