Solved: Extract specific part of text

ADP5 · ‎01-22-2024

Hello,

Suppose I have the following string output: abcdefgdd.mm.yyyyhijklmn

Let's say it's part of a longer text where "abcdefg" repeats.

I'd like to extract "dd.mm.yyyy", which always has the same number of characters even though the characters will differ, using "hijklmn" as a delimiter (("hijklmn") does not repeat in the text).

I need an expression that counts and extracts text from right to left. I tried using substring but negative index is not allowed. I somehow managed to do it applying slice to a shorter part of the entire string, but when I incorporated it from the test flow into the main flow with the whole text, it didn't give any result.

Any help would be much appreciated.

eliotcole · ‎01-22-2024

If 'abcdefg' is identical in each repeat, then you can split() on that.

Use a split() expression with the source of all the text as the first part, and 'abcdefg' is the splitter ... then skip the first pointless value ... something like this:

skip(
    split(
    	REFERENCE_TO_YOUR_SOURCE_TEXT,
    	'abcdefg'
    ),
    1
)

Replace 'REFERENCE_TO_YOUR_SOURCE_TEXT' with a reference to whatever holds the text.

That will create a simple array of strings where the first 10 characters will be what you want. So you could use a Select action with the above in the 'From' field, and (tap the little pen on the right) a 'Map' value of:

slice(
	item(),
	0,
	10
)

If you want to make that a usable date value, then you could also do this:

concat(
	slice(
		item(),
		6,
		10
	),
	'-',
	slice(
		item(),
		3,
		5
	),
	'-',
	slice(
		item(),
		0,
		2
	)
)

Here that is in action:

With the result:

I used this as my Select from' value:

skip(
    split(
    	outputs('SourceText'),
    	'abcdefg'
    ),
    1
)

View solution in original post

eliotcole · ‎01-22-2024

If 'abcdefg' is identical in each repeat, then you can split() on that.

Use a split() expression with the source of all the text as the first part, and 'abcdefg' is the splitter ... then skip the first pointless value ... something like this:

skip(
    split(
    	REFERENCE_TO_YOUR_SOURCE_TEXT,
    	'abcdefg'
    ),
    1
)

Replace 'REFERENCE_TO_YOUR_SOURCE_TEXT' with a reference to whatever holds the text.

That will create a simple array of strings where the first 10 characters will be what you want. So you could use a Select action with the above in the 'From' field, and (tap the little pen on the right) a 'Map' value of:

slice(
	item(),
	0,
	10
)

If you want to make that a usable date value, then you could also do this:

concat(
	slice(
		item(),
		6,
		10
	),
	'-',
	slice(
		item(),
		3,
		5
	),
	'-',
	slice(
		item(),
		0,
		2
	)
)

Here that is in action:

With the result:

I used this as my Select from' value:

skip(
    split(
    	outputs('SourceText'),
    	'abcdefg'
    ),
    1
)

wskinnermctc · ‎01-22-2024

If the text "abcdefg" is always the same length, then you can use slice.

slice({textvalue},7,17)

eliotcole · ‎01-22-2024

If 'abcdef' is not the same everytime then you are going to have a harder time, and might want to look into a way to use a regular expression somehow, @ADP5 ...

wskinnermctc · ‎01-22-2024

ah @eliotcole I just thought about using the periods as a reference 💡

@ADP5 here is an expression that doesn't matter the length of the text. It functions by referencing where the periods '.' are located. So long as the format dd.mm.yyyy stays the same with the '.' periods then this will work.

slice({textvalue},sub(int(indexOf({textvalue},'.')),int(2)),add(int(nthIndexOf({textvalue},'.',2)),5))

https://learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#...

eliotcole · ‎01-22-2024

Nice one, @wskinnermctc ... 🏆

Although ... 🤔 ... if I'm reading @ADP5's request rightly ( and I always get things wrong 😅 ) he's asking for each instance of the date in the text. Does yours account for that?

Take a look at the text that I fed in to my example ... that's potentially what I believe they are looking at ... and other more randomised versions, of course.

I can't think of a way to do this *easily* if the 'abcdefg' is not identical before each date.

However I could've sworn I saw something relating to regex recently ... maybe there is a regex hack out there?

eliotcole · ‎01-22-2024

Perhaps this needs some office scripts work like what @Paulie78 did here:

https://www.tachytelic.net/2021/04/power-automate-regex/

wskinnermctc · ‎01-22-2024

I don't know. I was just thinking simple terms like single text strings. I guess we'll see when OP replies.

(This is speculation and I'm not going to do it; more like entertainment purposes. But if it is like each instance in your example. I wonder if you could get away with counting the number of '.' periods and referencing them like that. *It must only contain periods at the date values* I bet it could be done in some wizard method, since if you split it, there would always be 3 sections for every date.

Anyways, making my head hurt think about it.)

eskay · ‎01-23-2024

@eliotcole

I think what you saw were Regex lookaheads.They would work like this:

Input: abcdefgdd.mm.yyyyhijklmn
Regex: .{10}(?=hijklmn)
Output: dd.mm.yyyy

.{10} = Take ten elements of whatever kind

(?=) = which are before the following

hijklmn = String

eliotcole · ‎01-23-2024

Aye ... that would be nice.

But I really do remember seeing them utilised in Power Automate / Logic Apps somewhere, @eskay, that was more what I was chatting about.

On that note, though, if @ADP5 can use Logic Apps, they might be able to leverage more, there. Either via a code block, or a Function App integration. If this is not a commonly run thing, it would only ever end up costing .0000000000000000000003 pence per run or whatever it is for the few actions it would cost. 😉

((( @wskinnermctc ☝️)))

AlexEncodian · ‎01-23-2024

@ADP5

You can quite easily achieve this with a single action using Encodian’s Regex Search action.

the regex expression would be:

\d{2}\.\d{2}\.\d{4}

Note this is a “Utility” action which only uses 0.05 of an Encodian credit. Therefore an Encodian plan with 500 credits can perform 10,000 Regex searches.

ADP5 · ‎02-06-2024

@eliotcole @wskinnermctc @eskay @AlexEncodian Thank you very much for your replies. I'm giving you the text below, to be more specific. The part I want to extract is in bold and it appears two times within the text. I only need one of the instances, it doesn't matter which one.

The date (which I want to extract) varies, so it's not the same in all texts. The reason I tried to extract it using a delimiter/index that is after it (on the right side of the date) and not before it (on the left side of the date) is because the delimiters/indices after it repeat multiple times in the text.

AlexEncodian · ‎02-06-2024

@ADP5

As I said, the regex query will pick this up regardless of delimiters, position within text or what the date is as long as it follows format ##.##.####

eskay · ‎02-07-2024

Since what you have is a Json format we can also work based on that without using Premium Features. You could read the text as Json, then Power Automate can help with getting to the "value" or "text" bracket and afterwads read the last x chars. Will code a proof of concept later today.

eskay · ‎02-07-2024

This would somewhat work:

It first transforms the input into a json object, that Power automate recognizes, then goes through all results, cuts out the last couple of characters and checks whether they have two . in them. Would still advise for the regex approach, since its a lot easier to maintain and read, but this could do it without any premium functionality.

Thanks @wskinnermctc for the idea of the . seperation, its a nice somwhat reliabe way to chech, whether im in the right result.

Have attached a sketch of how it could work. Havent checked it it works in practise, probably have a off by one in there in the substring part somewhere.

Extract specific part of text

Helpful resources

Community will be READ ONLY July 16th, 5p PDT -July 22nd

Summer of Solutions | Week 4 Results | Winners will be posted on July 24th

Check Out | 2024 Release Wave 2 Plans for Microsoft Dynamics 365 and Microsoft Power Platform

Updates to Transitions in the Power Platform Communities