Skip to main content

Is there a way to filter Datatable based on specific columns using inbuilt Datatable package or other packages? For example, Suppose we have a Datatable with columns “Name and ID”. I want to filter all the data with specific name and ID.

 

Note - I do not want to use loops since the number of data is high and also cannot write the data to excel and use excel as database query, due to certain limitations in the client environment.

 Hi @anandc ,

 

You can use WHERE in the SELECT query for this requirement. Below is an example. However, depending on the result set, you may still need to loop through the query result for getting each value from it.

 

SELECT * FROM RSheet1$$] WHERE (Name) = ‘John’ and (ID) = 1234 


 Hi @anandc ,

 

You can use WHERE in the SELECT query for this requirement. Below is an example. However, depending on the result set, you may still need to loop through the query result for getting each value from it.

 

SELECT * FROM FSheet1$$] WHERE (Name) = ‘John’ and (ID) = 1234 

Thanks for the answer @Padmakumar . Could you please help on which action can be used to pass this query? 


 Hi @anandc ,

 

You can use WHERE in the SELECT query for this requirement. Below is an example. However, depending on the result set, you may still need to loop through the query result for getting each value from it.

 

SELECT * FROM FSheet1$$] WHERE (Name) = ‘John’ and (ID) = 1234 

Thanks for the answer @Padmakumar . Could you please help on which action can be used to pass this query? 


Sorry. It seems I misread your question. Here is my recommendation.

Try either of the following methods and see any of them helps or not.
 

 Use "Search for a value" Action

  • If filtering by a specific Name or ID, you can use Search for a value action to locate matching rows.
  • This won’t create a new Data Table, but it helps extract specific positions for further processing.

 Use Python or VBScript for Filtering

  • Given A360 lacks built-in filtering, a Python package or VBScript can efficiently filter without loops:
import pandas as pd
df = pd.DataFrame(your_datatable)
filtered_df = df (dfi"Name"] == "XYZ") & (df="ID"] == 123)]

This extracts matching rows without iteration.


Consider SQL-like Filtering via External File Processing
​​​

  • If writing to Excel is restricted, can you store data in a text or CSV file and process it via SQL-like queries?
  • Example: Using MS SQL commands externally might work without loops.

Combining Actions Creatively

  • Sort the Data Table first to bring target values closer together.
  • Use Search for a value in sorted data, then extract relevant rows.

@anandc 

It's a great pity, but the DataTable package does not have a Filter action, unlike, for example, Power Automate Desktop, which significantly increases the efficiency of working with larger tables.

This is not an ideal solution, but my method involves using the "Search for a value" action from the DataTable package, which creates a list with the coordinates of the location in the table. Next, you need to map it to which cell in the table, but at least you don't need a loop! The downside is that "Search fro a value" works on the entire table, so there is a risk that the value appears in different columns, but at least I can quickly narrow down the list of records. So I hope I helped others with this problem, even if just a little. And it would be great if AA added such an essential action like "Filter" to the DataTable package.


Hello, 

Filtering a datatable is possible by executing a inner join on the wanted column, with a dummy datatable only having the wanted value(s) in its key column. Filtering on multiple columns at once is not possible like this, but need to be achieve by doing this sequencial for certain scenarios.

Kind regards,

Jan


Hello, 

Filtering a datatable is possible by executing a inner join on the wanted column, with a dummy datatable only having the wanted value(s) in its key column. Filtering on multiple columns at once is not possible like this, but need to be achieve by doing this sequencial for certain scenarios.

Kind regards,

Jan

I experimented a bit with this filtering using the Inner Join action and it actually works, but it requires some tweaking. First of all, the value we are looking for needs to be converted into a table, preferably of size 1X1. For this, it needs to have the name of the searched column matching the name of the column in the searched table, otherwise the Join will show an error. Then you also need to keep in mind that these will only be exact matches, meaning we won't have a chance, for example, to distinguish between uppercase and lowercase letters. For me, the solution is not ideal, and given such an obvious need, it requires a lot of work (potentially a separate module for filtering), so I still prefer the Search Data Table itself. But thanks for the idea, it works.


Reply