Invalid Value error when trying to use variable for row value in Data Table reference

[Randy_Colorado]

I have a working PAD step to Write to Excel worksheet using a set number reference in a Write the Valuse into currently active cell of an Excel Instance (%XcelEnergyRegEx[7]['PatternMatch']%). However, I recently had the row change when modifying the RegEx table to add additional rows and so added code to set a variable to the desired row value reference (BilledkWh) to better automate the program. Now when I try to change the step to reference the row variable I get an Invalid value error (%XcelEnergyRegEx[%BilledkWhRegExRow%]['PatternMatch']%).  I've verified the Variable contains "7" so am guessing the row value can't except a variable. 
Am I missing something in the formatting or is this not an option when referencing the row in a data table? Thanks in advance for any assistance.

[VishnuReddy1997]

Hi @Randy_Colorado ,

Try any the below 2 methods and atleast one of them work.Please find the below solution.

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards

Vishnu Reddy

[Randy_Colorado]

Vishnu,

Neither solution worked.  The first only duplicated the entire text into the activated Excel cell. The second solution returned an error stating the expression could not be evaluated.

[Deenuji]

@Randy_Colorado 

Here you go!! 

Syntax: 

%XcelEnergyRegEx[BilledkWhRegExRow]['PatternMatch']%

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 🚀

[Randy_Colorado]

Deenuji,

Your solution returned an error stating:  Property '7' of variable 'XcelEnergyRegEx' couldn't be evaluated.  Believe I had already tried that combination yesterday when attempting previous solutions provided by Vishnu.  Thanks for responding though

[Randy_Colorado]

Is there a way to reference the row other than the row number?  For instance, in the 'XcelEnergyRegEx_Row_7' table attachment, I have row 7 column 'Name' referenced 'BilledkWh'.  Not sure if there is a way to reference the 'Name' column value for the table row to get the 'PatternMatch" value desired.

 

[Deenuji]

@Randy_Colorado 

Another approach to retrieve your datatable row 7 involves iteration. You can utilize a foreach loop for this purpose, and then use a counter variable to access the value of the desired row.

How is your flow looks like? Have created  "BilledkWhRegExRow" variable and assigned number value 7 like below?

Then you have execute your write to excel action with correct syntax:

My final flow looks like below:

Other checks:

Before proceeding with this method, ensure that your flow is not enabled with Power FX syntax, as it may lead to syntax errors in various instances.

If it enabled, it may show like this yellow highlighted in top of your flow.

If its enabled, you can create new flow and disable during flow creation like below and recreate your flow:

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 🚀

[Randy_Colorado]

Deenuji,

Verified flow is not enabled with Power FX.  Using current code works however when trying to use variable [%BilledkWhRegExRow%] returns Invalid value when trying to save step or using variables ['BilledkWhRegExRow'] or [BilledkWhRegExRow] returns an error when running program as stated.

[Deenuji · Accepted solution]

@Randy_Colorado 

When you assign number from datatable to set variable it may consider that as text only instead of number. So after assigning, datatable value into variable then  include convert text to number action like below and try to execute your code now.

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 🚀

[Randy_Colorado · Accepted solution]

Deenuji,

The variable was 'text'.  Once I converted it to 'number' it worked perfectly.  Thanks for all your efforts.

regards,

Randy