Remove rows based on condition in different columns

[B_2023]

Hello,

First time posting 🙂

I have a two tables that look like this:

Table 1

ID with detail	ID without detail	Start date	End Date	Rate
AA1	A	01/07/2020	30/06/2023	10
BB1	B	01/10/2021	30/06/2023	15
CC1	C	01/12/2022	30/06/2023	10
AA1	A	01/12/2022	30/06/2023	25
BB1	B	01/09/2022	30/06/2023	30
AA2	A	01/12/2022	30/06/2023	42
BB2	B	01/11/2022	30/01/2023	50

*AA1 is in red to highlight the new x old rates.

Table 2

ID with detail	ID without detail	Index
AA1	A	1
AA2	A	2
CC1	C	3
BB1	B	4
BB2	B	5
BB3	B	6

Background: Table 1 accumulates all rates that have been practiced in the past with a history of the start and end dates. A new rate means that the previous one, even if not expired, cannot be used anymore. Table 2 needs to stay on a certain order according to the index and cannot have new lines added.

Problem: I need to bring only current rates from Table 1 to Table 2 without adding new lines on Table 2. 

Solution should look like this:

ID with detail	ID without detail	Index	Start Date	End Date	Rate
AA1	A	1	01/12/2022	30/06/2023	25
AA2	A	2	01/12/2022	30/06/2023	42
CC1	C	3	01/12/2022	30/06/2023	10
BB1	B	4	01/09/2022	30/06/2023	30
BB2	B	5	01/11/2022	30/01/2023	50
BB3	B	6	NA	NA	NA

What I tried: I tried merging queries, but that was adding new lines into table 2. Then I tried remove duplicates from table 1 but that would exclude lines without considering the dates. Just filtering also didn't work.

Tks

[v-stephen-msft · Accepted solution]

Hi @B_2023 ,

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.

[wdx223_Daniel]

= let a=Table.Group(Table1,"ID with detail",{"n",each Table.Max(_,"Start date")}) in Table.FromRecords(Table.TransformRows(Table2,each _&(a{[#"ID with detail"=[ID with detail]]}?[n]?[[Start date],[End Date],[Rate]]? ??[Start date=null,End Date=null,Rate=null])))

[B_2023]

Hey, Tks for the reply.

It is saying "An error occurred in the ‘’ query. Expression.Error: The name 'a' wasn't recognized. Make sure it's spelled correctly.".

What is "a" in the "(a{[#"ID with detail"=[ID with detail]]}?[n]?[[Start date],[End Date],[Rate]]? ??[Start date=null,End Date=null,Rate=null])))" bit please?