Ah okay now I understand.
If you don't know the amount of values you want to filter then I am not sure how to do it dynamically other than to use a script or macro on the Excel. Or have some logic that filters the Excel from the UI like you would as a user. So you would loop the list of values and choose them as filters one by one
Here is the script method: "Run .NET script" -action you can build C# script that takes in the ExcelData -table variable that you want to filter and a list of values you want to filter the table with.
So in my case the script takes in %ExcelData% and %ColumnAsList% and outputs %ExcelDataFiltered% -table. So here are the Script parameters:
The script filters the ExcelData tables Column2 with %ColumnAsList% -variables items.
The script itself:
.NET code to run:
DataView dv =
new DataView(dt1);
dv.RowFilter =
"Column2 IN (" +
string.
Join(
",", list1) +
")";
dt1 = dv.
ToTable();
If the column you are trying to filter is different than the Column2 then change it to be the correct one (Check the ExcelData variable column names to get the correct one).
Result:
Flow looks like this:
You can copy and paste this script action to your PAD flow:
@@CustomSummary: 'Input: ExcelData and List -variables. Output: ExcelDataFiltered'
Scripting.RunDotNetScript Language: System.DotNetActionLanguageType.CSharp Script: $'''DataView dv = new DataView(dt1);
dv.RowFilter = \"Column2 IN (\" + string.Join(\",\", list1) + \")\";
dt1 = dv.ToTable();''' @'name:dt1': ExcelData @'type:dt1': $'''Datatable''' @'direction:dt1': $'''InOut''' @'name:list1': ColumnAsList @'type:list1': $'''List''' @'direction:list1': $'''In''' @dt1=> ExcelDataFiltered
