RemoveMatchingRows

[DieterH]

Hello,

I tried to remove matching rows. I used the example out of documentation

Table.RemoveMatchingRows(Table.FromRecords(

{ [OrderID = 1, CustomerID = 1, Item = "Fishing rod", Price = 100.0],

[OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0],

[OrderID = 3, CustomerID = 2, Item = "Fishing net", Price = 25.0],

[OrderID = 4, CustomerID = 3, Item = "Fish tazer", Price = 200.0],

[OrderID = 5, CustomerID = 3, Item = "Bandaids", Price = 2.0],

[OrderID = 6, CustomerID = 1, Item = "Tackle box", Price = 20.0],

[OrderID = 7, CustomerID = 5, Item = "Bait", Price = 3.25],

[OrderID = 8, CustomerID = 5, Item = "Fishing Rod", Price = 100.0],

[OrderID = 9, CustomerID = 6, Item = "Bait", Price = 3.25] } ),

{[CustomerID = 3]}, "CustomerID")

I have 2 questions.

1. This code only works with the condition =. If I use [CustomerID <= 3] it brings up an error message. How can I make a condition like <= 3?

2. If I have a Date-Field instead of numeric like in the example above. It is not possible to use Date-functions, like [Date.Year(MyDate) = 2016]. Isn`t it possible to use Functions inside RemoveMatchedRows?

Thanks for your help

[v-shex-msft · Accepted solution]

Hi @DieterH,

For your requirement, you can refer to below formula:

>>1. This code only works with the condition =. If I use [CustomerID <= 3] it brings up an error message. How can I make a condition like <= 3?

let
    Source = Table.RemoveMatchingRows(Table.FromRecords(

{ [OrderID = 1, CustomerID = 1, Item = "Fishing rod", Price = 100.0],

[OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0],

[OrderID = 3, CustomerID = 2, Item = "Fishing net", Price = 25.0],

[OrderID = 4, CustomerID = 3, Item = "Fish tazer", Price = 200.0],

[OrderID = 5, CustomerID = 3, Item = "Bandaids", Price = 2.0],

[OrderID = 6, CustomerID = 1, Item = "Tackle box", Price = 20.0],

[OrderID = 7, CustomerID = 5, Item = "Bait", Price = 3.25],

[OrderID = 8, CustomerID = 5, Item = "Fishing Rod", Price = 100.0],

[OrderID = 9, CustomerID = 6, Item = "Bait", Price = 3.25] } ),

{[CustomerID = 3]}, each [CustomerID] <=3)

in
    Source

>>2. If I have a Date-Field instead of numeric like in the example above. It is not possible to use Date-functions, like [Date.Year(MyDate) = 2016]. Isn`t it possible to use Functions inside RemoveMatchedRows?

Yes, it is possible. If you want to keep use the remove mathch row funciton, you can modify the condition query to Date.Year([Date ColumnName]) <> 2016.

In addition, you can also use Table.SelectRows to achieve your requirement.

Table.SelectRows(Table.FromRecords(

{ [OrderID = 1, CustomerID = 1, Item = "Fishing rod", Price = 100.0],

[OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0],

[OrderID = 3, CustomerID = 2, Item = "Fishing net", Price = 25.0],

[OrderID = 4, CustomerID = 3, Item = "Fish tazer", Price = 200.0],

[OrderID = 5, CustomerID = 3, Item = "Bandaids", Price = 2.0],

[OrderID = 6, CustomerID = 1, Item = "Tackle box", Price = 20.0],

[OrderID = 7, CustomerID = 5, Item = "Bait", Price = 3.25],

[OrderID = 8, CustomerID = 5, Item = "Fishing Rod", Price = 100.0],

[OrderID = 9, CustomerID = 6, Item = "Bait", Price = 3.25] } ),

{[CustomerID = 3]}, each [CustomerID] >3 and DateYear([Date]) = 2016 )

Regards,

Xiaoxin Sheng

[lammens]

Thanks so much for your help, Xiaoxin, but there is something I don't understand about the syntax and semantics of this. The last two arguments to Table.RemoveMatchingRows are

{[CustomerID = 3]}, each [CustomerID] <=3

which seems to say to remove all rows where the CustomerID column is equal to 3, but then the matching criterion says CustomerID smaller than or equal to 3? How should I interpret this? They seem to specify two different sets of rows. I have read the documentation for Table.RemoveMatchingRows, but that didn't make me any wiser. Thanks for any pointers!