Create new table with TOPN values for each distinct column value

[Anonymous]

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:

Expected result:

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.

[Zubair_Muhammad · Accepted solution]

@Anonymous

Try this calculated table
Modelling Tab>>New Table

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

[Zubair_Muhammad · Accepted solution]

@Anonymous

Try this calculated table
Modelling Tab>>New Table

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

[Zubair_Muhammad · Accepted solution]

@Anonymous

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"

[Anonymous]

Thanks @Zubair_Muhammad 

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.