cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arutsjak90
Helper I
Helper I

Create top 2 table per category with sku

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

 

CategorySKUQTy
A110
A25
A315
A42
B56
B67
B79
C812
C97

 

Expected result (new table)

CategorySKUQTy
A315
A110
B79
B67
C812
C79

 

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

Thank you in advance!

1 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.

View solution in original post

3 REPLIES 3
Vera_33
Super User
Super User

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"

 

Vera_33_0-1615440188127.png

 

 

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. 

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.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 episode 5 with aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt will talk about the importance of Data Modeling.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!