Skip to main content

Notifications

Power Automate - Power Automate Desktop
Suggested answer

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

Posted on 26 Nov 2024 10:50:02 by
Hi
 
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:
  • SparkLight Profile Picture
    SparkLight on 26 Nov 2024 at 17:46:47
    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_ Profile Picture
    Deenuji_Loganathan_ 5,792 on 26 Nov 2024 at 15:29:53
    How to read values from datatable and set it in variables using Power Automate Desktop
     
    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 Profile Picture
    SparkLight on 26 Nov 2024 at 15:17:34
    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_ Profile Picture
    Deenuji_Loganathan_ 5,792 on 26 Nov 2024 at 13:42:05
    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 Profile Picture
    SparkLight on 26 Nov 2024 at 13:00:29
    How to read values from datatable and set it in variables using Power Automate Desktop
  • Deenuji_Loganathan_ Profile Picture
    Deenuji_Loganathan_ 5,792 on 26 Nov 2024 at 12:45:44
    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 Profile Picture
    eetuRobo 2,300 on 26 Nov 2024 at 12:37:03
    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 Profile Picture
    SparkLight on 26 Nov 2024 at 12:30:49
    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_ Profile Picture
    Deenuji_Loganathan_ 5,792 on 26 Nov 2024 at 12:07:50
    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 Profile Picture
    DBO_DV 4,158 on 26 Nov 2024 at 11:12:58
    How to read values from datatable and set it in variables
     
    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. 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November 2024 Newsletter…

November 2024 Community Newsletter…

Community Update Oct 28…

Power Platform Community Update…

Tuesday Tip #7 Community Profile Tips…

Welcome to a brand new series, Tuesday Tips…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 143,129

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 63,797

Leaderboard