Lookup value in Power Automate flow

[addepallimadhu]

We are looking to transform a data set (array of objects #1) to include looked up values from another data set (array of objects#2) .  For accomplishing this we need to create an array variable, for each item loop on array of objects#1 and filter on array of objects#2, retrieve the looked up value, construct an object with additional fields and insert into array variable. Is there any other way in Power Automate to accomplish this with fewer steps?

[ekarim2020]

HI @addepallimadhu 

Can you post sample data for

1. Array1
2. Array 2
3. What the resulting array should like

Ellis

[addepallimadhu]

@ekarim2020  - Let me know if the below helps. 

Array 1
[
{
"account" : "ABC123",
"txType" : "B",
"amount" : "200"
},
{
"account" : "ABC123",
"txType" : "S",
"amount" : "400"
},
{
"account" : "ZZZ876",
"txType" : "S",
"amount" : "200"
},
]

Array 2
[
{
"account": "ABC123",
"externalCode": "XYZ123"
},
{
"account": "ZZZ876",
"externalCode": "LMNEAPA"
}
]

Expected Output
[
{
"convertedCode" : "ABC123",
"txType" : "B",
"amount" : "200"
},
{
"convertedCode" : "ABC123",
"txType" : "S",
"amount" : "400"
},
{
"convertedCode" : "LMNEAPA",
"txType" : "S",
"amount" : "200"
},
]

[ekarim2020]

Hi @addepallimadhu ,

Can you please clarify the rules used to generate the expected output from array1 and array2.

 

 

Ellis

[addepallimadhu]

@ekarim2020 - We just need to lookup account from Array1 in Array 2, get the externalCode from Array2 and the use for convertedCode in the Expected Output. The attributes txType and account are used as-is from Array1. Hope this helps.  Actually, there is small error in my original reply. Apologies.. The convertedCode should be XYZ123 for the first two elements.  Here is the correct expectOutput.    

[
{
"convertedCode" : "XYZ123",
"txType" : "B",
"amount" : "200"
},
{
"convertedCode" : "XYZ123",
"txType" : "S",
"amount" : "400"
},
{
"convertedCode" : "LMNEAPA",
"txType" : "S",
"amount" : "200"
},
]

[ekarim2020 · Accepted solution]

Hi @addepallimadhu ,

Here is one approach to the problem. The solution transforms array 2 into a JSON object from where you can lookup the external code using the account code:

 

This is the expression  used in the compose action:

json(slice(replace(string(body('Select')),'},{',','),1,-1))

I'm converting the output of the Select action into a string, then replacing the text "},{" to "," , then using the slice function to remove the "[" and "]". Finally the JSON function converts the JSON "object" (which is still a string) into a real JSON object. 

Select 2 is used to produce the required data structure:

The expressions used in Select 2 are:

convertedCode: outputs('Compose')?[item()?['account']]
txType: item()?['txType']
amount: item()?['amount']

Here is the runtime output of the demo flow:

 

Hope this helps.

Ellis
____________________________________
If I have answered your question, please mark the post as ☑️ Solved.
If you like my response, please give it a Thumbs Up.
My Blog Site