Select row with MAX value per category Power BI

Question

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 |

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

Answer 1

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"

If you want to learn more about Power BI, then read the Power BI Tutorial. Also, enroll in Power BI online training Course to become proficient in this BI tool.