cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vicky_umr
Frequent Visitor

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

Row data:

ype IDTCommentsWO IDDate
1A1237/24/19 9:30 AM
2B1237/24/19 10:30 AM
3C1237/24/19 11:00 AM
4dfs4567/23/2019 5:21PM
1erfd4567/21/2019 10:21PM
2asd4567/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 IDCommentsWO IDDate
3C1237/24/19 11:00 AM
4dfs4567/23/2019 5:21PM

 

Thank you.

1 ACCEPTED SOLUTION
HotChilli
Super User II
Super User II

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'

View solution in original post

3 REPLIES 3
HotChilli
Super User II
Super User II

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'

View solution in original post

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]))

Hi @HotChilli 

I'd like to value your solution.

It works like a charm.

 

Best Regards
Maggie

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors