Power Platform Community Forum Thread Details

Hello!

I'm currently pulling the data I need into an excel spreadsheet (parse data into new row), but there are times where there are multiple lines of data I need to grab. I don't know how to make it loop/ or repeat until it reaches the end of the email. The format is always the same, but I'm a bit lost on how to configure it (or it can even be done). I'm still new to Power Automate, and I think it's something simple I'm missing.

Here's how the data comes over:

Here is how I'm parsing it into the spreadsheet:

and here is my current process flow:

Here is my current formulas for retriving the info:

trim(first(split(last(split(body('Html_to_text'),'Shipment Client Keys:')),'Post failed with a message:')))

trim(first(split(last(split(body('Html_to_text'),'Post failed with a message:')),'This message')))

I'm missing something, and I've searched the boards here. There was only one other post about a situation like this but the post didnt provide anyone any infomation in order to help. If it can't be done, I can accept that and will have to figure out another process for capturing this info. But I thought I'd at least ask the question... 🙂

Thank you!

Categories:

Building flows

What you're asking here is doable, but it's not really straightforward.

You can approach it with the split() function like you have already, or you can use a combination of substring() and indexOf()

Using split() you would already know that if you split on "Shipment Client Keys" you'll have an array of strings that start with the text you want, except the first line which you'll need to discard.

So, for example, split on that text, then you can filter out the first row using skip():

skip(split(body('Html_to_text'),'Shipment Client Keys:'),1)

Now you have an array of strings that starts with the text you want, you can add a Select action.

Your key-value pairs will need to be the Shipment Client Keys and the Invalid address book entry

So, call the first key-value pair "shipmentKeys" add this expression as the value.

trim(substring(item(),0,indexOf(item(),'Post failed'))

This will effectively strip off everything from "Post failed" onwards for each row of the array, leaving you with the client keys.

The next key-value pair you can call "addressBookEntry" or whatever and use this formula to get the text out of the string:

substring(
		item(),
		add(
			indexOf(
				item(),
				'Post failed'),
			29),
		sub(
			indexOf(
				item(),
				' is not a valid'),
			add(
				indexOf(
					item(),
					'Post failed'),
				29)
			)
		)

This will parse out the relevant text (of variable length) from the array item. You may need to wrap trim() around that too.

What you're left with when the flow runs is an array like this

[
 {
 "shipmentKeys": "3362344, M356231",
 "addressBookEntry": "5030383"
 },
 {
 "shipmentKeys": "3362388, I035715",
 "addressBookEntry": "DDM0619"
 }
]

You can then do what you want with this array, such as insert a new row within an Apply to each. If you want the dynamic content then run it through a Parse JSON action with the above code as the sample payload.