Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
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"

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. 

Related questions

Browse Categories

...