How to find the length of text strings between 2 keywords?

[Anonymous]

Hello Evryone,

I have a requirement to find  number of characters between 2 text string. 

Am able to fetch the fix number of characters after the first text string(keyword) but how to extract total number of characters excluding space between 2 keywords here "Given" and "When"?

Note: These 2 keywords can be in one line or 2 different lines.

Ex 1.

Given    When 

Ex 2.

Given I am an Administrator

When I view Requirement Table

Then It should include IDD rules

Below is not the correct DAX. Just trying to fetch 50 characters out of it.

givenText length =

var BeginText = SEARCH("given",Requirements[Acceptance Criteria],1,BLANK())

var EndText = SEARCH("when",Requirements[Acceptance Criteria],1,BLANK())

RETURN

IF(ISBLANK(BeginText),BLANK(), MID(Requirements[Acceptance Criteria], BeginText,50))

TIA

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

M query may be a better choice.

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JYyxCoAwDAV/5dHZn3ASVxEcSoekBgzYiLXW31fqrXec927QKoYRlECGfk1qepVM5cgudN4tW9NV5cEk561ZkljBTLxLK/4DEYGZ0foYI9YPF8IL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Inserted Text Between Delimiter" = Table.AddColumn(#"Changed Type", "Text Between Delimiter", each (Text.BetweenDelimiters([Column1],"Given","When")), type text),
    #"Replaced Value" = Table.ReplaceValue(#"Inserted Text Between Delimiter"," ","",Replacer.ReplaceText,{"Text Between Delimiter"}),
    #"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "Custom", each if [Text Between Delimiter] = "" then null else Text.Length([Text Between Delimiter]))
    
in
    #"Added Conditional Column"

First use Text.BetweenDelimiters function to split a column by delimiters.

Then replace the space.

Finally, the Text.Length function is used to get the character length.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @V-lianl-msft 

This works for me. Thank you so much.

[Greg_Deckler]

@Anonymous - Take your mid result, turn it into a table using Text to Table (word version), filter out blank rows and then use COUNTROWS across the table.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Text-to-Table/td-p/1312929

[Anonymous]

I just wanted the text between "given" and "when". Then I can find the length of the text string (excluding space) using LEN() and TRIM().

Note: These 2 keywords can be in one line or 2 different lines.

Ex 1.

Given    When 

Ex 2.

Given I am an Administrator

When I view Requirement Table

Then It should include IDD rules

But I am stuck to find the total string between "given" and "when". Sometimes it is giving correct result and sometimes exceeds beyond "when"

Below DAX is not giving correct result. Where am I going wrong?

given-when Text  =

var BeginText = SEARCH("given",lower(Requirements[Acceptance Criteria]),1,BLANK())

var EndText = SEARCH("when",lower(Requirements[Acceptance Criteria]),1,BLANK())

RETURN

IF(ISBLANK(BeginText),BLANK(), MID(LOWER(Requirements[Acceptance Criteria]), BeginText,EndText))

TIA