Fetching data from Excel Table

[autoperfect93]

Hello All,

I have the below .qid file with several rows of information.

On row 5 I have the Cparty name which is an old one and I need to change it to the new one. To do so I need to open an excel table and check which is the new name corresponding to the old one shown above.

Can this be done via lookup by Power Automate?

Also I need to retrieve the corresponding Risk Center number (10 digit number on "Column C") and paste it in the .qid file on row 17 after "|" symbol. 

So I assume that I will need 2 lookups. One to find the new name (based on the old name in the .qid file) and Second to find the corresponding risk center number (the 10 digit number on the last column).

Here is an example of a completed file:

Also I would like to note that the Counterparty name on row 5 will be different for every .qid file so it is not a static data.

Please share any ideas or directions if any of the above is possible in Power Automate.

[v-yueyun-msft · Accepted solution]

Hi, @autoperfect93 

According to your description, you want to update your .qid file based on the Excel file. And thanks for yoru sample data .

Here are the steps you can refer to .

This is my test data in SharePoint Document :

This is my flow:

(1)The first three actions:

split(base64ToString(outputs('Get_file_content')?['body']?['$content']) , decodeUriComponent('%0D%0A'))

And for the '\r\n', you can refer to :
Solved: Split at \r\n\r\n - Power Platform Community (microsoft.com)

(2)The "Filter array" and "Initialize Variable" actions:

split( split(base64ToString(outputs('Get_file_content')?['body']?['$content']),'Cparty=')[1] , decodeUriComponent('%0D%0A') )[0]

(3)The "Apply to each" action:

trim(split(item(),'=')?[0])

body('Filter_array')?[0]?['Counterparty new name']

concat( item() , body('Filter_array')?[0]?['Risk Center'] )

(4)The last two actions:

join(variables('MyData'),decodeUriComponent('%0D%0A'))

You can also adjust the flow according to your actual situation, I hope this helps you!

Best Regards,

Yueyun Zhang

[autoperfect93]

Hi @v-yueyun-msft ,

Thank you! The solution you provided is working. Is it possible to implement this solution for cases when there are multiple deals inside a single .qid file ?

Say for example we have 5 deals inside a single .qid file. We need to change the Cparty name field on each deal and put the correct Risk Center number. Our source data is again an excel table. Is this possible in Power Automate ?

Not every deal has the same Cparty. So on Filter Array step we need to filter each Cparty for each deal and replace the name based on the new names in the excel table. Also the Risk Center numbers are different for each Cparty.