You’re offline. This is a read only version of the page.
Hi - is it possible to find a specific cell(s) based on the value? It only checks the formula, can't find a neat workaround. Thanks!
Works like a charm, thanks a million! 🙂
Hi Agnius - thanks for your input, I'll look into it this weekend and get back to you 🙂
You should build a For each loop on %DataTableMatches% to process each match. The %CurrentItem% will then contain a single row of indexes. You can then get the row index for your data table as %CurrentItem['Row']% inside the loop. The issue here, however is that PAD returns these indexes as strings for some reason, so you need to convert them to numeric values first before using it. Use Convert text to number to do that.
Once you have converted your row index to a number and, say, stored it into %TextAsNumber% which is the default name of the variable, you can do %ExcelData[TextAsNumber][0]% to get the value from your table in the specific row that you matched and column 1 (because indexes are 0-based).
If you want them all in a list, you would need to use Create new list before the loop.
So, in general, the flow (after getting the matches) should look somewhat like this:
-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.
I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.
Any kind soul that could help me with this? 😇
Also, is there an option to delete columns from the Data Table?
Hi Anginus - I've tried the Find or Replace in Data Table action - it returns the data table with indexes of the "TO DO" cells, yes, it works however I'm not sure how to get the data from different columns on the same row - Could you please advise what actions to use?
Example - Source Data Table:
Find or Replace in Data Table - retrieves the following data table - lookup value "TO DO":
Can you please advise what actions/loops to use to return a list with values from Column 1?
Based on the source data table I'd need to have back a list with the following values:
112403902
112403903
PS: feel free to use the sample sheet I sent above.
Thanks! 🙂
Appreciated, thanks.
Noted, thanks!
Hi @farb
In addition to what @Agnius , @UshaJyothi20 explained here, i would recommend to try Linq queries against your dataset using Run .net script action, refer the link for that below
https://www.linkedin.com/pulse/how-run-net-script-action-empowers-data-table-power-automate-bysani/
It is quite advanced but would be helpful when dealing with large data 🙂
Nived N 🚀
LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌
What @UshaJyothi20 suggests is that you should read the data into a data table variable using Read from Excel worksheet, which is a good idea. However, building a loop to look for the value is extremely inefficient. Especially if the actual production data you'll work with is large. Once you have read the data, you can use Find or replace in data table to find the matches. Since Read from Excel worksheet will retrieve values and not formulas, you will be able to use Find or replace in data table to actually find the value.
The result will be a data table that contains row and column indexes for the matched cells. These will be 0-based, so you will need to increment them by 1 (or by 2, if you make Read from Excel worksheet store the first row as column headers, and not part of the data itself). And they will for some reason be stored as strings, so you will need to convert them to a number using Convert text to number to use them. But once you've done that, you can process the matches easily.
-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.
I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.
#1
WarrenBelz
146,660
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
66,004
Most Valuable Professional
Share
Report
All responses (
Answers (