How to create the key value pair from the excel table

[Abhimanyuthite1]

Hi,

I wanted to create the Key-Value pair from the excel table. Yes there is 'Select' from Data operation but it's output also come in array. Also required how we can use key to get the value.

Here is Excel Table:

Select action's output-

[

{"KEY_FIRST" : VALUE_FIRST},
{"KEY_SECOND" : "VALUE_SECOND"}

]

I required Key - Value pair like,
{

"KEY_FIRST" : VALUE_FIRST,
"KEY_SECOND" : "VALUE_SECOND"

}

If anyone knows your guidance would be very helpful for me.

Thank you,

[VJR]

Hi @Abhimanyuthite1 

Regarding your question on "how we can use key to get the value"

You can use the "Get a row" action and pass the key in the "Key Value" last box shown below.

[Abhimanyuthite1]

Hi @VJR ,

Thanks for responding, but the thing is that there are so many rows are present in the excel sheet and for each time using Get Row leads to the power automate error "your quota is replenished", so after that I have to wait for the full fill the quota again. This is also a consume more time.
For the avoiding hitting so many of spreadsheet API's because of getting each row Required the Map Collection which able to create from the Excel which take only one time to hit the excel and the data would be in Map collection.

Thanks & regards.
 

[mahoneypat]

Here is one way to do it after your Select step.  Create an empty array variable called ResultsArray and then add an Apply to Each step on the Select output with this expression.

{" <expression1> ":" <expression2> "}

expression 1 = item()?['Key']

expression 2 = item()?['Value']

Pat

[mahoneypat]

Or, if you really just have two rows and need that format, you could do it in a single Compose step instead of an ATE.  In this case, using [0] and [1] to index them.

body('Select')?[0]?['Key']

body('Select')?[0]?['Value']

body('Select')?[1]?['Key']

body('Select')?[1]?['Value']

Pat

[wrs007]

@Abhimanyuthite1 

What is P2 in your example. The answer is what is a Key Value?

[takolota]

@Abhimanyuthite1 @wrs007 @mahoneypat @VJR 

You can flatten any JSON array into a single JSON object with this expression...

json(replace(replace(replace(string(body('Insert_Action_Name')), '[{', '{'), '}]', '}'), '},{', ','))

I've used this method in several flow templates to create things I can more easily reference or to perform a type of table Join in Power Automate.