Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
alau
Helper I
Helper I

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

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: 

 

Capture1.JPG

 

 

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:

 

Capture2.JPG

 

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

 

Hopefully someone can help! 

 

Thanks in advance

 

Anthony

BI newbie

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

Hi, @alau 

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

vangzhengmsft_0-1648437547896.png

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:

vangzhengmsft_1-1648437632107.png

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.

View solution in original post

6 REPLIES 6
v-angzheng-msft
Community Support
Community Support

Hi, @alau 

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

vangzhengmsft_0-1648437547896.png

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:

vangzhengmsft_1-1648437632107.png

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
Community Champion
Community Champion

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
Super User
Super User

@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

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

Capture3.JPG

 

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?

@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.

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

 

Capture4.JPG

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.