Answered

Search for a value in table and store a different value in that row as a variable

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Thanks in advance for any help.

I am extracting a table from a webpage and have it stored as a table variable.

I use the find or replace in data table action to find a specific value in column A. I want to then return the value of column B of that row and store it to a variable. I understand you can use the column reference returned by find/replace action but I can't reason how that works.

For clarity, I want to search for "apple" and have "fruit" stored as a variable to use later in the flow.

Column A	Column B
apple	fruit
carrot	vegetable

Categories:

Power Automate Desktop

All responses (2)

Answers (1)

Sort by

Verified answer

Agnius Bartninkas Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Here's a sample flow that can do what you need:

And here's a snippet that you can copy and paste directly into PAD to get the actions generated automatically:
Variables.CreateNewDatatable InputTable: { ^['Column A', 'Column B'], [$'''apple''', $'''fruit'''], [$'''carrot''', $'''vegetable'''] } DataTable=> DataTable Variables.FindOrReplaceInDataTable.FindItemInDataTableByColumnIndex DataTable: DataTable AllMatches: True ValueToFind: $'''apple''' MatchCase: False MatchEntireCellContents: True ColumnNameOrIndex: 0 DataTableMatches=> DataTableMatches Text.ToNumber Text: DataTableMatches[0]['Row'] Number=> RowIndex SET type TO DataTable[RowIndex]['Column B']

What it does:
Creates a data table with the sample values you provided (you might not need this, if you already have it as inputs from somewhere)
Uses the Find or replace in data table action. You are probably already aware of how this works
Retrieves the row index of the first match from the %DataTableMatches% variable and converts it to a number. This is needed, because the Find or replace in data table returns the indexes as strings. I have no idea why it does that, but it does. You need to convert the index to a number in order to be able to use it to retrieve the value from the other column.
Assigns the value from 'Column B' and the matched row to a variable called %type%. You can use whatever variable name there - this is just a sample.
The idea is that you use the index from the matches to get the value from the same row, but a different column.
-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution.
If you like my response, please give it a Thumbs Up.
If you are interested in Power Automate, you might want to follow me on LinkedIn at https://www.linkedin.com/in/agnius-bartninkas/
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks very much, I came to this conclusion ultimately after combing many different posts and documentation, but hopefully this solution can help someone else in future.