Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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:

 

 

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.

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    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's avatar
    Zubair_Muhammad
    7 years ago

    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"

3 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        Thanks Zubair_Muhammad :smileyvery-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.