Back

Explore Courses Blog Tutorials Interview Questions
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"

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

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

28.4k questions

29.7k answers

500 comments

94.1k users

Browse Categories

...