Exclude a value with Filter Table in Excel

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?image

1 Like

@Jenni_K Can you try the Following Steps :

  1. Use Read Range to Read the Excel File as a Datatable, say DT.

  2. 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.

  1. Try using the nameArray variable in the Filter Options and check if it works

Thank you very much for giving me the idea of using an array variable as an input to the FilterOptions.

Creating a DT and ruling out the value which I want to exclude. Then assigning it to an array works for me.

Have a nice day!

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.