cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
SMR89 Frequent Visitor
Frequent Visitor

Create new table with TOPN values for each distinct column value

Hi all,

 

I've got a table loaded in Power BI which I need to filter by top 5 values of each product. I need the new table to be loaded in the data model.

 

I show you an example:

 

Source table:

 

Captura1.PNG

 

Expected result:Captura2.png

 

 

 

I have tried to use the TOPN function but the result is only displaying the overall top 5 values. I am not been able to find the way to split the top5 by a column value.

 

It doesn't matter if it is solved either by DAX or by Power Query.

 

Could you please help me finding a solution?

 

Thank you so much in advance.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Create new table with TOPN values for each distinct column value

@SMR89

 

Try this calculated table
Modelling Tab>>New Table

 

Calculated Table =
GENERATE (
    SELECTCOLUMNS ( VALUES ( 'Table1'[Product] ), "Product_", [Product] ),
    CALCULATETABLE ( TOPN ( 5, 'Table1', [Value], DESC ) )
)
Super User
Super User

Re: Create new table with TOPN values for each distinct column value

@SMR89

 

With Power Query, you can use GroupBy function alongwith Table.MaxN to get the desired results

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc67DcAgDATQXVxThH9ShoyB2H8N8BEOUVjys6yza5VXjFitS5qZdKNuyo/KVDgUMVlK2F7KCFYV5A1tPvtime2KLXNRs0nHM6DHj2Q4DtlIftolRoH/V60D", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, Attribute2 = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Attribute2", Int64.Type}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {{"ALL", each Table.MaxN(_,"Value",5), type table}}),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"Attribute2", "Value"}, {"Attribute2", "Value"})
in
    #"Expanded ALL"
3 REPLIES 3
Super User
Super User

Re: Create new table with TOPN values for each distinct column value

@SMR89

 

Try this calculated table
Modelling Tab>>New Table

 

Calculated Table =
GENERATE (
    SELECTCOLUMNS ( VALUES ( 'Table1'[Product] ), "Product_", [Product] ),
    CALCULATETABLE ( TOPN ( 5, 'Table1', [Value], DESC ) )
)
Super User
Super User

Re: Create new table with TOPN values for each distinct column value

@SMR89

 

With Power Query, you can use GroupBy function alongwith Table.MaxN to get the desired results

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc67DcAgDATQXVxThH9ShoyB2H8N8BEOUVjys6yza5VXjFitS5qZdKNuyo/KVDgUMVlK2F7KCFYV5A1tPvtime2KLXNRs0nHM6DHj2Q4DtlIftolRoH/V60D", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, Attribute2 = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Attribute2", Int64.Type}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {{"ALL", each Table.MaxN(_,"Value",5), type table}}),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"Attribute2", "Value"}, {"Attribute2", "Value"})
in
    #"Expanded ALL"
SMR89 Frequent Visitor
Frequent Visitor

Re: Create new table with TOPN values for each distinct column value

Thanks @Zubair_Muhammad Smiley Very Happy

 

I have tried both DAX and Power Query solution and they both worked properly.

 

I will use the Power Query solution in my report, since I consider it more efficient. This way I don't have duplicated data loaded in the data model.