How to read values from datatable and set it in variables using Power Automate Desktop

(1) Share

Report

Posted on by SparkLight

I have a datatable which is created by extracting data from webpage. I want to store the values from the table into variables, the datatable has 3 rows, 1 column

#	results
0	Alen
1	John
2	Eric

I want to read the contents and save it in variables which can be used for further data processing in power fx query. I have tried using %Datatable% with indexing but nothing seems to work.

Categories:

Process Advisor

All responses (10)

Answers (0)

SparkLight 14 on at

Like (0)

Report

How to read values from datatable and set it in variables using Power Automate Desktop

Hi Deenu,

Thank you for the solution to the original post and it worked :)

Another problem which I am facing is to read the related entity's attribute using dataverse in power automate desktop.

I am using below fetchquery to read the related entity attribute but its throwing error

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
<entity name="account">
<attribute name="name" />
<attribute name="industrycode" />
<attribute name="emailaddress1" />
<attribute name="accountnumber" />
<attribute name="accountid" />
<order attribute="name" descending="false" />
<link-entity name="contact" from="parentcustomerid" to="accountid" link-type="inner" alias="ad" />
<link-entity name="activitypointer" from="regardingobjectid" to="accountid" link-type="inner" alias="ae" />
<link-entity name="contact" from="contactid" to="primarycontactid" visible="false" link-type="outer" alias="a_410707b195544cd984376608b1802904">
<attribute name="emailaddress1" />
</link-entity>
</entity>
</fetch>

This is what I have used to read emailaddress of contact
${First(ListRecordsWithOrganizationResponse.value).ad.emailaddress1}
Suggested answer

Deenuji_Loganathan_ 6,087 on at

Like (0)

Report

How to read values from datatable and set it in variables using Power Automate Desktop

Hi @SparkLight,

I hope I have addressed your original issues (reading values from a datatable) in the Power FX-enabled flow. If my answer has helped you resolve your issues, please consider marking that answer for this topic.

In case if you require any further assistance on the same flow, please feel free to write back with full issue details.

Thanks,
Deenuji Loganathan 👩‍💻
Power Automate Desktop Community Champion 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and like my suggestion ❤️ Your feedback supports future seekers 🚀
SparkLight 14 on at

Like (0)

Report

How to read values from datatable and set it in variables using Power Automate Desktop

Hi Deenu,

Thank you for the response. I have managed to do all this but there is a problem in reading the related entity attribute :(
Suggested answer

Deenuji_Loganathan_ 6,087 on at

Like (0)

Report

How to read values from datatable and set it in variables using Power Automate Desktop

Hi SparkLight,

First, list your Dataverse rows without any filter to display the data as follows:

Copy the schema name of your Dataverse column (for example, 'cr1dd_patientname') and place it in the filter rows. Then, add the value of your datatable column using the format '$(variablename)'.

Thanks,
Deenuji Loganathan 👩‍💻
Power Automate Desktop Community Champion 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and like my suggestion ❤️ Your feedback supports future seekers 🚀
SparkLight 14 on at

Like (0)

Report

How to read values from datatable and set it in variables using Power Automate Desktop
Deenuji_Loganathan_ 6,087 on at

Like (0)

Report

How to read values from datatable and set it in variables using Power Automate Desktop

It worked!! however, how can I use these values inside the list rows query for power automate

for ex,

(contains(unq_entity/unq_name, #value 1 from datatable#) and unq_entity/unq_postcode eq value 2)

Hi SparkLight,

Are you asking how to read these values in Power Automate cloud flows? If possible, please share a screenshot of what you are referring to for our understanding.

Thanks,

Deenu
Suggested answer

eetuRobo 2,723 on at

Like (0)

Report

How to read values from datatable and set it in variables using Power Automate Desktop

Since you are using Power Automate Desktop with Power Fx enabled you can't use the regular PAD syntax with percent signs. So normally you could read "Eric" from your example by using %DataTable[2]['result']% but Power Fx does not work with that syntax.

With Power Fx you can read cell item with ReadCell(DataTableName, RowIndex, ColumnIndex)
In your example to get "Eric":
=ReadCell(DataTable, 1, 2)

Then you could loop it by having LoopIndex variable in the RowIndex position. Like so:
=ReadCell(DataTable, LoopIndex, 2)
SparkLight 14 on at

Like (0)

Report

How to read values from datatable and set it in variables using Power Automate Desktop

Hi Deenu,

Thank you for the response :)

It worked!! however, how can I use these values inside the list rows query for power automate

for ex,

(contains(unq_entity/unq_name, #value 1 from datatable#) and unq_entity/unq_postcode eq value 2)
Suggested answer

Deenuji_Loganathan_ 6,087 on at

Like (2)

Report
How to read values from datatable and set it in variables using Power Automate Desktop

Hi @SparkLight,

Please follow the below sample flow and approach for your reference:

Code(Copy and paste the below code into your power automate desktop):

WebAutomation.LaunchEdge.LaunchEdge Url: $fx'https://afd.calpoly.edu/web/sample-tables' WindowState: WebAutomation.BrowserWindowState.Maximized ClearCache: False ClearCookies: False WaitForPageToLoadTimeout: $fx'=60' Timeout: $fx'=60' PiPUserDataFolderMode: WebAutomation.PiPUserDataFolderModeEnum.AutomaticProfile TargetDesktop: $fx'{"DisplayName":"Local computer","Route":{"ServerType":"Local","ServerAddress":""}}' BrowserInstance=> Browser WebAutomation.ExtractData.ExtractHtmlTable BrowserInstance: $fx'=Browser' Control: $fx'html > body > div:eq(1) > div:eq(0) > table:eq(1)' ExtractionParameters: {[$fx'Name', $fx'Telephone', $fx'Email', $fx'Office'], [$fx'', $fx'', $fx'', $fx''] } PostProcessData: False TimeoutInSeconds: $fx'=60' ExtractedData=> DataFromWebPage LOOP FOREACH CurrentItem IN $fx'=DataFromWebPage' SET Firstcolumn TO $fx'=Index(CurrentItem, 1)' END

Thanks,
Deenuji Loganathan 👩‍💻
Power Automate Desktop Community Champion 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and like my suggestion ❤️ Your feedback supports future seekers 🚀
Suggested answer

DBO_DV 4,430 on at

Like (0)

Report
How to read values from datatable and set it in variables

Hey @SparkLight,

What you need is the index function.

Look at my test :
ClearCollect(colTest,Table({'#':0,Result:"Test1"},{'#':1,Result:"Test2"},{'#':2,Result:"Test3"})); Set(var1,Index(colTest,1).Result); Set(var2,Index(colTest,2).Result); Set(var3,Index(colTest,3).Result);

The Clearcollect is to mimic your table and then you can create the variables.
If you know tht it's always three records this is the easiest way there is to achieve what you need.

If this solvede your porblem please accept it as solution so others can find it as well.
If it helped in any other way consider liking it so we can keep supporting eachother.