Remove rows based on condition in different columns

Hi Anonymous ,

You can merge Table 2 with Table 1. Matching columns are [ID with detail] and [ID without detail].

Expand the Table column.

Group by [ID with detail] as follows.

The latest dates grouped by [ID with detail] are returned.

Expand the All Rows and then add a custom column for filtering.

Keep the value of 1 in the custom column and get the final result.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRRKM8syVBISS1JzMxR0oEJ5JeWIInlpaRWKMXqRCs5OhoC+Y5AbAjlG0H5RmC+szNI3hmIjcF8JycQ3wmITaB8IyjfFMo3hvLNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID with detail", type text}, {"ID without detail", type text}, {"Index", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"ID with detail", "ID without detail"}, Table, {"ID with detail", "ID without detail"}, "Table", JoinKind.LeftOuter),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"Start date", "End Date", "Rate"}, {"Table.Start date", "Table.End Date", "Table.Rate"}),
    #"Grouped Rows" = Table.Group(#"Expanded Table", {"ID with detail"}, {{"All Rows", each _, type table [ID with detail=nullable text, ID without detail=nullable text, Index=nullable number, Table.Start date=nullable date, Table.End Date=nullable text, Table.Rate=nullable number]}, {"Max Date", each List.Max([Table.Start date]), type nullable date}}),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"ID without detail", "Index", "Table.Start date", "Table.End Date", "Table.Rate"}, {"ID without detail", "Index", "Table.Start date", "Table.End Date", "Table.Rate"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All Rows", "Custom", each if [Table.Start date]=[Max Date] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Max Date", "Custom"})
in
    #"Removed Columns"

You can also download my attachment for more details.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.