Extract Text from String

[DavieJoe]

Hi,

I’m trying to extract a string from a column, it is an error code and it will be found in between the text “errorcode:” and “errormessage”. Not every row will contain an error message. The error code will be a number ranging from 1 digits up to 5 or 6 (Potentially more)

This is for my boss and it is a Tabular Model build. I’m aware that this sort of text extraction is best handled in Power Query but unsure if the Tabular Model can do this or not so it may need to be a DAX solution.

If I’ve not included enough information then please let me know.

Error Message Extract Text.pbix (28.1 KB)

Thanks

David

[Melissa]

Hi @DavieJoe,

See if one of these approaches works for you. I can’t help myself and started in PQ

Table.AddColumn( #"Changed Type", "ErrCode", each 
    let
      mySplit = Splitter.SplitTextByAnyDelimiter({":", ","}), 
      Result  = mySplit([ErrorCodes])
    in
      try List.Skip(Result, List.PositionOf(Result, "errorcode")){1}? 
      otherwise null
)

Or you could try this as a calculated column

ErrCode (CC) =
VAR _1 = SUBSTITUTE( ErrorTable[ErrorCodes], ":", "|" )
VAR _2 = SUBSTITUTE( _1, ",", "|" )
VAR _3 = PATHCONTAINS( _2, "errorcode" ) // returns true or false
VAR _4 = PATHITEM( _2, 4, TEXT ) // items appear to have a fixed position
RETURN
    IF( _3 = TRUE(), _4 )

Here’s your sample file:
Error Message Extract Text.pbix (29.0 KB)

I hope this is helpful.

[DavieJoe]

Hehe…I’d expect nothing less than a marvelous PQ solution from you @Melissa

Thanks for this, will pass it onto my boss and see if it works for him.

Looks like it works perfectly, thank you

[Greg]

Hi @DavieJoe. Here’s another DAX solution using a repositioned IFERROR functions.

ExtractErrorCode2 = 
VAR _ErrorCodeTextPosition = IFERROR( FIND( "errorcode:", ErrorTable[ErrorCodes] ), -1 ) 
VAR _ErrorCodeValuePosition = IF( _ErrorCodeTextPosition = -1, -1, _ErrorCodeTextPosition + 10 )
VAR _ErrorMessageTextPosition = IFERROR( FIND( ",errormessage", ErrorTable[ErrorCodes] ), -1 ) 
VAR _Result = IF(
    _ErrorCodeTextPosition = -1, BLANK(),
    MID(
        ErrorTable[ErrorCodes],
        _ErrorCodeValuePosition,
        _ErrorMessageTextPosition - _ErrorCodeValuePosition
    )
) 

RETURN
_Result

Hope this helps.
Greg

eDNA Forum - Extract ErrorCode.pbix (29.5 KB)

[DavieJoe]

Thanks @Greg appreciate a different method for my learning, will check it out.

Thanks for taking the time out to offer a different solution.

David

[jamie.bryan]

Hi @Greg

Hope this is ok to put on a solved question, but I’ve had a dig into your solution and just a bit confused by some of the values it’s spitting out. I’ve broken down each section into its own column and have got some weird values like the highlighted:

Thanks,

[Greg]

Hi @jamie.bryan. Absolutely … ask away … the whole point of posting detailed explanations is to foster discussion, so here goes:

Power BI by default summarizes rows with similar values in a visual; if you add an [Index] column to the table, then add the [Index] to the table visual, the code works as expected.

Hope this helps.
Greg
eDNA Forum - Extract ErrorCode 2.pbix (37.2 KB)