0 votes
1 view
in BI by (17.6k points)

How to select the row with a max value per category in M of Power BI. Suppose we have table:

+----------+-------+------------+

| Category | Value |    Date    |

+----------+-------+------------+

| apples   |     1 | 2018-07-01 |

| apples   |     2 | 2018-07-02 |

| apples   |     3 | 2018-07-03 |

| bananas  |     7 | 2018-07-04 |

| bananas  |     8 | 2018-07-05 |

| bananas  |     9 | 2018-07-06 |

+----------+-------+------------+

Desired results are:

+----------+-------+------------+

| Category | Value |    Date    |

+----------+-------+------------+

| apples   |     3 | 2018-07-03 |

| bananas  |     9 | 2018-07-06 |

+----------+-------+------------+ 

Here is a start table for PBI:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyEktVtJRMgRiIwNDC10Dc10DQ6VYHSQ5I2Q5I1Q5Y2Q5Y7BcUmIeEIIkzZElTdAkLZAlTdEkLZElzZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category = _t, Value = _t, Date = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Value", Int64.Type}, {"Date", type date}})

in

    #"Changed Type" 

I wonder if there is a way to come to desired results in subsequent steps within only one table, by adding some magic column IsMax:

+----------+-------+------------+-------+

| Category | Value |    Date    | IsMax |

+----------+-------+------------+-------+

| apples   |     1 | 2018-07-01 |     0 |

| apples   |     2 | 2018-07-02 |     0 |

| apples   |     3 | 2018-07-03 |     1 |

| bananas  |     7 | 2018-07-04 |     0 |

| bananas  |     8 | 2018-07-05 |     0 |

| bananas  |     9 | 2018-07-06 |     1 |

+----------+-------+------------+-------+ 

1 Answer

0 votes
by (47.2k points)

There are two approaches:

  • First approch is one-liner snipped in R transformation:

library(dplyr) output <- dataset %>% group_by(Category) %>% mutate(row_no_by_category = row_number(desc(Date)))

  • Second approach is completely done  in PBI:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyEktVtJRMgRiIwNDC10Dc10DQ6VYHSQ5I2Q5I1Q5Y2Q5Y7BcUmIeEIIkzZElTdAkLZAlTdEkLZElzZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category = _t, Value = _t, Date = _t]), #"Grouped rows" = Table.Group(Source, {"Category"}, {{"NiceTable", each Table.AddIndexColumn(Table.Sort(_,{{"Date", Order.Descending}} ), "Index",1,1), type table}} ), #"Expanded NiceTable" = Table.ExpandTableColumn(#"Grouped rows", "NiceTable", {"Value", "Date", "Index"}, {"Value", "Date", "Index"}), #"Filtered Rows" = Table.SelectRows(#"Expanded NiceTable", each ([Index] = 1)) in #"Filtered Rows"

Related questions

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...