Forum Discussion
Remove rows based on condition in different columns
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
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.
3 Replies
= 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])))
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?
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.
