Unanswered

How can i get column's name according to its value

(0) Share

Report

Posted on by ChrisG24

Hello!

I have a DataTable with column names included. I am iterating through the table and therefore on each iteration I get a DataRow. Then I am iterating with another for each loop through every DataRow to get the values of the columns in the DataTable. The result is correct but how can I also extract the name of the column under which the value is taken from. For example my output result is "David" which comes from a column with the name "First Name". How can i get that "First Name" value?

Categories:

Power Automate Desktop

I have the same question (0)

All responses (5)

Answers (0)

Sort by

CU16071609-1 6,255 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@ChrisG24
If I grasp your requirement accurately, you have data stored in a datatable, and you wish to retrieve all the data along with their respective column names.

In that case, I believe I've achieved a similar objective but with a different approach. Here are the steps I took:
1. I read an Excel file that includes all the account details.
2. Using the "Launch Excel and Read All Values from Excel" action, I extracted the data from the Excel file and stored it in a datatable named "ExcelData."
3.1 In a for-each loop, I iterated through the account details and displayed the column names. Since I know the column names and their order, I hardcoded them in the message box. Additionally, I mentioned the corresponding values by referencing their index value in the message box.

Flow Details:

Code:
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''Y:\\Accounts_Details.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
LOOP FOREACH CurrentItem IN ExcelData
Display.ShowMessageDialog.ShowMessage Title: $'''Account Details''' Message: $'''Full Name is %CurrentItem[0]%
First Name is %CurrentItem[1]%
Last Name is %CurrentItem[2]%''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed
END
Excel.CloseExcel.Close Instance: ExcelInstance

--------------------------------------------------------------------------------------------------------------

Certainly! If your objective differs from what I've described, please provide more details or context regarding your requirements.

Also If you could share screenshots of your datatable or any relevant information, I'd be happy to assist you further and provide guidance based on the specifics of your situation.

Thanks,
Deenu

------------------------------------------------------------------------------------------------------------------------

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.

Was this reply helpful? Yes No
ChrisG24 2 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for your reply but this isn't exactly what i am looking for. I am using this code to iterate both through the DataTable rows and then through the columns of each DataRow. This way i am getting the value of the interception between row and column, a.k.a cell value, but i also want to know which column does this cell value belong to.

Was this reply helpful? Yes No
CU16071609-1 6,255 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@ChrisG24 _ Could you please share your datatable(pls hide if any sensitive info ) screenshot if possible?

Furthermore, I don't understand the logic behind looping through your datarow values again when we can directly reference them by name, as you did for personRow['firstname'].

Was this reply helpful? Yes No
CU16071609-1 6,255 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@ChrisG24
**************************************Ignore this answer - Follow the below one**********************************

I'm still not entirely clear on your exact requirement. However, if you need to determine the column name of a cell value, you can hardcode the column name based on the Index counter variable.

Was this reply helpful? Yes No
CU16071609-1 6,255 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@ChrisG24
Here you go for optimized approach (Not hardcode values):
I've added a variable named "counter," and during the iteration, I accessed the column name of the current iterated value by using "dtmatrixData.Columns[Counter]".

Code:
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''Y:\\Accounts_Details.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
SET Person_Name TO $'''%''%'''
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> dtMatrixData
LOOP FOREACH PersonRow IN dtMatrixData
SET Person_Name TO $'''%PersonRow['FirstName']%+\' \'+%PersonRow['LastName']%'''
SET Counter TO 0
LOOP FOREACH QualificationColumn IN PersonRow
SET CellValue TO $'''%dtMatrixData.Columns[Counter]%: %QualificationColumn%'''
Variables.IncreaseVariable Value: Counter IncrementValue: 1
END
END
Excel.CloseExcel.Close Instance: ExcelInstance

If I've successfully resolved your queries, please consider accepting this as the solution and giving it a thumbs up. Your feedback is greatly appreciated!

Was this reply helpful? Yes No