I need to filter an excel table but exclude one of the names (e.g. Harry) in it with using Filter Table activity. It does a very good job by filtering the given input, but when it comes to exclude one of the items in the list, I couldn’t figure out how to do it.
In this example, I can write all the other values except Harry in FilterOptions and it’s done. But my case is that I don’t know the exact values, they are dynamically reproduced in the selected excel.
Any suggestions? If doing this is not possible with this activity, than is there any other best practices?
Use Read Range to Read the Excel File as a Datatable, say DT.
Then use this Expression in an Assign Statement :
nameArray = DT.AsEnumerable.Where(Function(x)Not(x(“Name”).ToString.ToLower.Equals(“harry”))).Select(Function(y)y(“Name”).ToString).ToArray
Where nameArray is an Array of String Type.
Try using the nameArray variable in the Filter Options and check if it works