Filtering data from excel table

[autoperfect93]

Dear Community,

I have created a flow which is being triggered whenever a new .QID file is received in Outlook. The flow is reading the content of the file (via Get content step) and then splitting it to an array as shown below:

As you can see inside the .QID file I have details of foreign exchange deals. What I need the flow to do is to change the "Cparty" line where it has to fetch the new counterparty name from an excel table in SharePoint and replace the old counterparty name in the .QID file with the new one coming from the excel table.

I have managed to do this but only if the file contains only one deal. To do so I used Filter Array step below:

Currently this works only if I have one deal in the file but it does not work if I have 2 or more deals. 

Is there any possibility to make this work with many deals?

[grantjenkins]

This is how I'd go about building the flow, based on the following assumptions.

• "EXENAME=FXDEAL" only appears once (first item in the array).
• Each block of data contains 19 rows.
• The order of the properties are always the same $DoMenu, ticket, sectype, Cparty, etc.

See full flow below. I'll go into each of the actions.

Compose contains your array data (including two blocks of data).

Filter array uses the output from the Compose and removes the first row (EXENAME=FXDEAL) plus any empty rows.

//From (remove the first row)
skip(outputs('Compose'), 1)

//Condition
item()

Apply to each uses the chunk expression to retrieve 19 rows of data at a time (each of our blocks of data contains 19 rows). So, the first time through the loop it will use the first 19 rows, then the next 19 rows, and so on.

chunk(body('Filter_array'), 19)

In this example, I'm just output each of the Cparty values. Compose Cparty contains the following expression.

//Get the forth item (index 3) and remove the 'Cparty=' text.

replace(item()[3], 'Cparty=', '')

When we run the flow, the Apply to each will iterate over our two block of rows in our array (in this example) and output the Cparty name for the current block of data.

 ----------

Another option, if you wanted an array of objects (key/value pairs) for each block of data, you could use a Select directly after your Filter array using the following expressions. Note that you would do this for all the properties you want to use.

//From
chunk(body('Filter_array'), 19)

//Map (Keys)
first(split(item()[0], '='))
first(split(item()[1], '='))
first(split(item()[2], '='))
...

//Map (Values)
last(split(item()[0], '='))
last(split(item()[1], '='))
last(split(item()[2], '='))
...

After running the flow you would get the following output from the Select. You could then just iterate over the Select in your Apply to each and use the key/value pairs to extract the data you need.

And the actual data. Note that I only added the first 5 properties for this example. You could map all 19 properties, or just the ones you want to use.

[
  {
    "$DoMenu": "mfnew",
    "ticket": "12345",
    "sectype": "XXX",
    "Cparty": "SN02-COFFEE 1031127215",
    "Entity": "XXX"
  },
  {
    "$DoMenu": "mfnew",
    "ticket": "12345",
    "sectype": "XXX",
    "Cparty": "SN02-COFFEE 1031103124",
    "Entity": "XXX"
  }
]

[autoperfect93]

Hello @grantjenkins ,

Thanks for the shared options. I think I will use the first option. 

I have 2 questions currently:

Since there are different types of deals and based on the type of the deal the number of lines may differ. So for Non Deliverable Forward deal I have 19 items (rows) but for FX Forward Onshore deals I have 17 items (rows.). In a single .QID file I may have the two deal types meaning that I need to consider deals with different number of items (rows) based on the deal type. Can this consideration be implemented in Power Automate?

Also after we have selected the Cparty names on step "Compose Cparty" how can we replace the old names selected with the ones in Column B "New Counterparty name". Note that not every counterparty name needs to be replaced. 

For example in the first deal I have Cparty = SN02-COFFEE 1031127215 which needs to be replaced with Cparty = SN02-COFFEE

[grantjenkins]

Ok, a completely different approach given what you mentioned about the different number of properties, etc. and to finish of the step to replace the old values.

I've got the following Excel file with the old and new values.

See full flow below. I'll go into each of the actions.

Compose contains your raw data as before.

Initialize variable creates a string variable called data that uses the output from Compose.

List rows present in a table retrieves the rows from our Excel Table.

Apply to each iterates over each of the rows from the Excel Table.

Compose Replace replaces the old value with the new value for the current row we are iterating over using the following expression.

replace(variables('data'), item()?['Old Counterparty name'], concat('Cparty=', item()?['New Counterparty name']))

Set variable sets the data variable to the output from the Compose Replace.

After the Apply to each we have Compose Result that sets our output back to JSON ready for you to save to a new file. It uses the following expression.

json(variables('data'))

After running the flow we should get the following output from Compose Result.

[autoperfect93]

Hi @grantjenkins ,

In my case I first receive the .QID File in Outlook Inbox (as an attachment) and the get the content of the .QID file via Get Attachment (V2) step after which I have Compose step to display the data. Given the way my flow is being triggered and is reading the file I cannot initialize a variable after Compose. Here is the error message:

[autoperfect93]

To add more clarify of what I want to achieve I will add the Word document here:

[grantjenkins]

You need to initialize your variable at the top level (before the Apply to each) and then use Set variable within the Apply to each.

[autoperfect93]

Hi @grantjenkins ,

My flow runs successfully but the Cparty name is not amended with the new name. Here is the flow:

The Compose 4 which resembles the end result is showing the same names in Cparty field

Am I doing something wrong ?