How to group by ID and max of column and get another column value

[Anonymous]

I have a table with multiple columns. One column has ID which has duplicate values for different dates and days.

ID	Date	Day	Title
123	10/11/2022	Tuesday	XYZ
111	12/11/2022	Tuesday	XYZ
123	09/11/2022	Monday	XYZ
123	11/11/2022	Friday	ZXK

I want to get a table where I have 1 row for each unique ID, Max of date for that ID and corresponding Day and Title. For now, I am able to group by ID and select Max of Date, but when I select Day, it's giving duplicate rows. I have got the expected outputs below. Please help.

ID	Date	Day	Title
123	11/11/2022	Friday	ZXK
111	12/11/2022	Tuesday	XYZ

[WanderingBI]

Hi, 

the first step is to find the maximum date by grouping ID by Max[Date]:

You can then use this table to filter down your source table.

[WanderingBI]

Two solutions:

Solution 1

Easier solution where you just use the GUI.

1. Create a new query called FilterTable where you group the original table by ID using Max of Date. Rename the column with the date to "MaxDate".

2. Then merge the filterTable Query with the original Query on "ID" "Date"/"MaxDate" and select "Inner" (only keep matching rows).

This will yield the two rows that you are looking for.

You can open the sample Power BI file here:

https://1drv.ms/u/s!AjX6-gq4HfZQg_9-Hi25c1e4QqO-Nw?e=UBCwvM 

Solution 2

This method is to  first create a filter table that has the highest date for each ID by grouping by max date.

Then insert custom columns in the original table to do some filtering. In the first column I insert the filterTable into each row.

You can copy my example code into the advanced query editor and follow the transformation steps:

let
// Input table
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WMjQyVtJRMjTQNzTUNzIwMgJyQkpTi1MSK4GsiMgopVgdoCJDQ5AiI/yKwCYZWCIp8s3Pw6YGqAChxq0oE6ImKsKbODWxAA==", BinaryEncoding.Base64), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [ID = _t, Date = _t, Day = _t, Title = _t]
  ), 

  // Real code starts here
  removeDuplicates = Table.Distinct(Source), 
  tableToFilter = Table.TransformColumnTypes(removeDuplicates, {{"ID", Int64.Type}, {"Date", type date}, {"Day", type text}, {"Title", type text}}), 
  
  // Branch off with a filter Table
  filterTable = Table.Group(tableToFilter, {"ID"}, {{"MaxDate", each List.Max([Date]), type nullable date}}), 
  
  filterColumn = Table.AddColumn(tableToFilter, "Filter Column", (outerRow) => Table.SelectRows(filterTable, (innerRow) => innerRow[ID] = outerRow[ID])), 
  matchIDandHighestDate = Table.AddColumn(
    filterColumn, 
    "isHighest", 
    each if List.Contains(filterTable[ID], [ID]) and List.Contains(filterTable[MaxDate], [Date]) then true else false
  ), 
  onlyKeepHighest = Table.SelectRows(matchIDandHighestDate, each ([isHighest] = true))
in
  onlyKeepHighest