Datatable For Each Loop - Accessing records

(0) Share

Report

Posted on by tar-us

Hi,

I am using Power Automate Desktop (PAD) to load data from a CSV into a Datatable variable, successfully.

My task is to iterate (using a For Each loop) through each record in the table, to capture certain fields and use them as parameters in a subflow. All the community and official content online suggests to use the format "%Datatable['Fieldname']%" as the notation to access the value of a field for the current record inside a For Each loop, this does not work! It just returns the input "%CurrentItem['Fieldname']%" as the output. Similarly I have attempted to return a record outside of the For Each loop, using the notation %Datatable[0]% where 0 is the first record index, the videos online show this working but on my PAD it does not.

On a seperate note, many community users show a sub section to the "Variable" menu item, that is specific to working with Datatables, I cannot see this...?? Was this feature revoked or?

NB I am using a Premium license.

Categories:

Power Automate Desktop

All responses (4)

Answers (1)

Sort by

tar-us 9 on at

Like
a
(0)

Report
Copy link

Link copied!

Update: I realise now that this is because PowerFX (Preview) was turned on, in a flow without this turned on, the notation in the documentation works.

That leads me to wonder how we can achieve this looping action when PowerFX is enabled?
Verified answer

CU16071609-1 6,255 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

@tar-us
In order to access the datatable within a Power Fx enabled flow, we utilize the syntax "=ReadCell(CurrentItem, ColumnIndex)". The column indexes start from 1, 2, 3, and so forth.

For instance, if I wish to read the first column of my datarow, the syntax is as follows:
"=ReadCell(CurrentItem, 1)"
Flow Details:
Code:
SET Column1Value TO $fx'%""%'
SET Column2Value TO $fx'%""%'
SET Column3Value TO $fx'%""%'
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $fx'C:\Users\2440621\Documents\Number_Check.xlsx' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.ReadFromExcel.ReadAllCells Instance: $fx'=ExcelInstance' ReadAsText: True FirstLineIsHeader: False RangeValue=> ExcelData
LOOP FOREACH CurrentItem IN $fx'=ExcelData'
SET Column1Value TO $fx'=ReadCell(CurrentItem, 1)'
SET Column2Value TO $fx'=ReadCell(CurrentItem, 2)'
SET Column3Value TO $fx'=ReadCell(CurrentItem, 3)'
Display.ShowMessageDialog.ShowMessage Title: $fx'Alert' Message: $fx'=Column1Value' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed
END

-----------------------------------------------------------------------------------------------------------------------
If I've resolved your query, please consider accepting it as the solution and giving it a thumbs up to help others find answers faster.
tar-us 9 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi Deenuji that very helpful, thank you!

Do you know where this is documented by MS - I searched high and low to no avail.

Cheers
CU16071609-1 6,255 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@tar-us

As you said, Its still in preview so documentations are not up to the mark right now.

But pls refer the below links.
https://learn.microsoft.com/en-us/power-platform/power-fx/formula-reference-desktop-flows
https://learn.microsoft.com/en-us/power-automate/desktop-flows/power-fx