Get all records of all other columns at the latest date within each distinct WO_ID

[vicky_umr]

Row data:

ype IDT	Comments	WO ID	Date
1	A	123	7/24/19 9:30 AM
2	B	123	7/24/19 10:30 AM
3	C	123	7/24/19 11:00 AM
4	dfs	456	7/23/2019 5:21PM
1	erfd	456	7/21/2019 10:21PM
2	asd	456	7/22/2019 8:21PM

Requirements:

With distinct WO_ID, get the records of the latest date and associate with the Type_ID and comments.

Output:

Type ID	Comments	WO ID	Date
3	C	123	7/24/19 11:00 AM
4	dfs	456	7/23/2019 5:21PM

Thank you.

[HotChilli · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc7BCoAgDAbgV4mdA93USm/WOeguHgLrAfL9oYkRhYf9h/0fbCEAQg+eB0lxjoK0QNtZp2TnV4h9AOL93AiUH1KKpSXo5Es0F+nMnNoMFSlBkplxhFtF5ZnjOtNXYVV872Xloz3/FFU1PSje", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Type IDT" = _t, Comments = _t, #"WO ID" = _t, Date = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type datetime}}, "en-US"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"WO ID", type number}, {"Type IDT", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"WO ID"}, {{"Latest D", each List.Max([Date]), type datetime}, {"All", each _, type table [Type IDT=number, Comments=text, WO ID=number, Date=datetime]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Type IDT", "Comments", "Date"}, {"All.Type IDT", "All.Comments", "All.Date"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All", "Custom", each if [Latest D] = [All.Date] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"All.Type IDT", "All.Comments", "WO ID", "Latest D", "All.Date", "Custom"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"All.Date", "Custom"})
in
    #"Removed Columns"

Here's the advanced editor code.  The names and types might be a bit different at your side.

Basically, group the data on 'WO ID' and aggregate MAX of Date and add aggregate of 'All Rows'. Expand the 'All' column.  Remove the rows where the date is not equal to the latest date for each 'WO ID'

[vicky_umr]

Appreciate on your help!

I did some research on this similar question. And I don't want to change the table.

So here's what I did to solve this problem: 

Reference: https://community.powerbi.com/t5/Desktop/Fill-a-column-based-on-a-value-in-two-other-columns/td-p/52...

Step 1: Add column using DAX

Latest Type ID = CALCULATE(MAX(WO_ACTIVITY[TYPE_ID]),FILTER(ALL(WO_ACTIVITY), WO_ACTIVITY[WO_ID] = EARLIER([WO_ID])))

Step 2: Add another column

Latest comment = CALCULATE(MAX(WO_ACTIVITY[COMMENTS]), FILTER(ALL(WO_ACTIVITY), WO_ACTIVITY[WO_ID]=EARLIER([WO_ID]) && WO_ACTIVITY[TYPE_ID] = WO_ACTIVITY[Latest Type ID]))

[v-juanli-msft]

Hi @HotChilli 

I'd like to value your solution.

It works like a charm.

Best Regards
Maggie