How to extract a string of text if it appears in a column?

[alau]

Hi Power BI experts, need help with a problem I can't seem to solve.

I have a column containing an audit trail of changes made to every product of our item master. Within this column called "LOG CHANGE", there can be one or more changes, or even nothing: 

I want Power BI to add another column to my table, so that if there is a change to a product's condition code (the change will appear as stk-condition-code="?"|"?", where the ? can be any letter of the alphabet), Power BI extracts out that portion of the audit trail only. As illustrated in my samples, that string stk-condition-code="?"|"?" can appear in any portion/order of the log, so it needs to be dynamic enough to search for it. Also, it needs to be dynamic enough to extract the whole string, regardless of what letter of the alphabet the "?"s are.  

So this ultimately is my desired output:

I tried a combination of "SEARCH", "FIND", "LEN" but no combo of those worked. 

Hopefully someone can help! 

Thanks in advance

Anthony

BI newbie

[v-angzheng-msft · Accepted solution]

Hi, @alau 

In fact, since PowerQuery does not recognize the wildcard character "?" and multiple """, the solution provided by @truptis  does not work.

You can try this solution below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZFBa8JAEIX/iuw5gdRLTz2sqyZW0FJXpGRziOtSQoMJ2eRQ8Md3djtiSDNUL2HgfXnvzWyaMs4CZtuvUFfnU9EW1Rmmk3lRTCg2uUwUk4rdg2RBymYIdtY04WdTdXX6lAG3X0vBvY3HhBtwnlPeW+cNPwUDw9IZJo3qomj6fIEOG7ofeADBsd0CsfyoQ13m1gKxww0EBg0sJMqczugjLmVJ3EAefJm3LSb1W8y9tLhdKCZM3LJA7ncjJsJLcnyRX/mBp0x6b7Qi2iRrH7l8pzKl18c25l6ZgfLfXSMs9Pq3xNSf5APB+PqGNi+NDdvv2gyXi6miHIENAnVTaAPfqjXacYDcCKqxM1khkmU/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PRODUCT = _t, #"LOG CHANGES" = _t, #"Desired text extract" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PRODUCT", type text}, {"LOG CHANGES", type text}, {"Desired text extract", type text}}),

    
    #"Added Custom" = Table.AddColumn(#"Changed Type", "positoinStart", each Text.PositionOf([LOG CHANGES], "stk-condition-code=")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if [positoinStart] = -1 then "" else Text.Range([LOG CHANGES],[positoinStart])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom1", "positionof", each Text.PositionOf([Custom],",")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom3", "Result", each if [positionof] = -1 then [Custom] else Text.Middle([Custom], 0,[positionof])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"positoinStart", "Custom", "positionof"})
in
    #"Removed Columns"

Result:

If you wish to use regular expressions, then you can refer to this article

Using regular expressions in power bi desktop.

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[truptis]

Hi @alau ,

You can do it in Power query. Refer this:

Create a new column in transform data and paste the below code:

if Text.Contains([ColumnName], "stk-condition-code="?"|"?") then [column name] else " "

If this doesn't work, then write the below dax:

CustomColumn = IF(

 ISERROR(

  SEARCH("stk-condition-code="?"|"?", TableName[ColumnName])

 ),

 Columnname,

 " "

)

Here columnname is your LOG_CHANGE column.

Please hit a thumbs up & mark it as a solution if it works. Thanks.

[amitchandak]

@alau , In dax

If (Search("stk-condition-code", [LOG CHANGE],,0) >0, [LOG CHANGE], blank())

you can also think about Text.Contains in power Query , Split by delimiter etc

[alau]

Thanks for the reply @amitchandak , but that didn't give me what I want (* unless I misunderstood you).

That managed to return those cells that contains stk-condition-code="?"|"?" , but I want Power BI to not just give me the entire content, but only the portion of the string that reads stk-condition-code="?"|"?". 

Any further ideas?

[amitchandak]

@alau , Try a new column like

new column =
Var _1 = Search("stk-condition-code", [LOG CHANGE],,0)
Var _2 = if(_1>0, Search(",", [LOG CHANGE],2,0)
var _3 = if(_2 =0, len([LOG CHANGE]),_2)
return
if(_1>0, MID([LOG CHANGE],_1,_3), Blank())

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[alau]

What am I doing wrong? I tried adding a new column and this is the syntax error: