cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Abhimanyuthite1
New Member

How to create the key value pair from the excel table

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:

Abhimanyuthite1_0-1635232025120.png

 

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,

 

6 REPLIES 6
VJR
Super User
Super User

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.

 

VJR_2-1635249076217.png

 

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
Solution Sage
Solution Sage

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']

 

mahoneypat_0-1635806083757.png

mahoneypat_1-1635806183108.png

 

Pat

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about the Power Platform, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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']
 

 

mahoneypat_0-1635815932356.png

mahoneypat_1-1635815939401.png

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about the Power Platform, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


wrs007
New Member

@Abhimanyuthite1 

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

takolota
Super User
Super User

@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.

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (3,391)