how to check a specific value is exist in Datatable PAD?

(1) Share

Report

Posted on by Sanket1

357

HI Team,

I want to check current item value is exist in DataTable or not if exist then skip the current transaction.

Thanks in advance!

Categories:

Power Automate Desktop

All responses (14)

Answers (1)

Sort by

CU16071609-1 6,255 Moderator on at

Like
a
(2)

Report
Copy link

Link copied!

@Sanket1

You can use the below "Find or replace in data table" like below and find the required item is exists or not.
If it exists you will get in the Datatablematches variable.

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀
NathanAlvares24 1,714 Moderator on at

Like
a
(2)

Report
Copy link

Link copied!

Hi @Sanket1 !

Let me take an example.

Say I have this data in one DataTable:

And this is my flow:

What this flow does is, that it will check for a specific value given by the user. Here it is given in variable, 'SearchValue'. If it matches, skip it by using "Next loop" action. If not, insert into another data table or basically do the rest of the necessary process.

So basically if you want to skip a transaction which matches the condition, just use "Next loop" action.

I hope this helps.
CU16071609-1 6,255 Moderator on at

Like
a
(2)

Report
Copy link

Link copied!

@Sanket1

If we opt for "For each" or any other loop it takes more time if you pass large number of rows table than "Find or replace in data table" action.

Please refer the below example for your understanding:

My Data table:
Thought process:
I selected my data table as the input table and chose 'find' as the search mode because I do not want to replace anything. Then, I specified "Alex" as the text to search for throughout the entire data table, which is why I mentioned it in the 'search by' field.

Output:
It shows "Alex" found at 1st Row (First Row starts at 0th Index) and 0th Column.

In case if you want to search on any specific index or name then you can change "Search By" into "On column"

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀
Sanket1 357 on at

Like
a
(2)

Report
Copy link

Link copied!

Thank you so much @Deenuji and @NathanAlvares24
let me apply in my flow then I will get back to you.
Sanket1 357 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @Deenuji and @NathanAlvares24

My workflow is like this

1- read excel data
2- Filter it - dt_FilterEmpdata

for each in dt_FilterEmpdata
-----CurrentItem['Pan_Card']

Set var - PanCardNo = CurrentItem['Pan_Card']

now I am searching file[ 3 files in 3 folder] and by using pan card number and merge it

Every time [ loop] i am writing another excel (ReportExcel ) that

this excel contain PanNo, EmpID, File A, FileB, FileC .
_______________________________________________________________________________________________________________

If somehow while running bot failed, When I again run the bot then I want check PanNo is Exist in ReportExcel if exist then skip that transaction move to next loop( Transaction)
CU16071609-1 6,255 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
@Sanket1

Follow the below steps:

Mt Data table looks like below:

Code:

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\deenu\\OneDrive\\Documents\\ConvertedCSVExcel.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: False RangeValue=> ReportExcelTable LOOP FOREACH CurrentItem IN ReportExcelTable IF IsEmpty(CurrentItem['PanNo']) THEN # PAN No is empty so your logic goes here ELSE # You skip the loop and move to next transcation END END

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀
Sanket1 357 on at

Like
a
(1)

Report
Copy link

Link copied!

i am looping through empData within that I want to check

This is my flow

CurrentItem is EmpData Excel That I want to check whether it is exist in reportExcel file(dt)

Not separate
Sanket1 357 on at

Like
a
(1)

Report
Copy link

Link copied!

It would be great if you provide me the script which will check value is exist in DT,
If exist the store in var boolean value true or false
Verified answer

CU16071609-1 6,255 Moderator on at

Like
a
(2)

Report
Copy link

Link copied!

@Sanket1

Please follow the below approach:

Find or replace in data table(Refine Search By as per your column index or name):

You will receive a flag indicating whether your PAN exists or not in the "Is_Exists" field.

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀
Sanket1 357 on at

Like
a
(1)

Report
Copy link

Link copied!

Thank you so much @Deenuji