Create New Column based on Value found between Quotation Marks

[quincy_p]

Hi - I am tring to create a new column as I need to pull an "asset ID" number from a error dialogue line. 

The table is called 'Work Order ALL' and the the nubmer is found in [Integration_Result__c], the lines in this column would look like:

Error CS_ServiceRequest_PUB.Create_ServiceRequest API Programming Error (CS_ServiceRequest_UTIL.Validate_Customer_Product): The value "111448692" for field p_customer_product_id is invalid.

And I need to pull the 111448692 into a new column. I tried the below formula but keep getting this error: 

       OLE DB or ODBC error: Query (3,25) the search text provided to function 'FIND' could not be find in the given text

NumericValue = 
    VAR IntegrationResult = 'Work Order ALL'[Integration_Result__c]
    VAR CleanedValue = 
        MID(
            IntegrationResult,
            FIND("0", IntegrationResult, 1),
            FIND(" ", IntegrationResult, FIND("0", IntegrationResult, 1)) - FIND("0", IntegrationResult, 1)
        )
    RETURN VALUE(CleanedValue)

TIA!

[EylesIT · Accepted solution]

@quincy_p, here is my suggested solution.

I am using a test data table of one row with one column called [Integration_Result__c] which contains the full text of the error message you posted

Error CS_ServiceRequest_PUB.Create_ServiceRequest API Programming Error (CS_ServiceRequest_UTIL.Validate_Customer_Product): The value "111448692" for field p_customer_product_id is invalid.

I then created a Calculated Column with this DAX expression:

assetID = 
    VAR search1 = "(CS_ServiceRequest_UTIL.Validate_Customer_Product): The value """
    VAR search2 = """ for field p_customer_product_id is invalid"
    VAR pos1 = FIND(search1, YourTable[Integration_Result__c], 1, 0)
    VAR pos2 = FIND(search2, YourTable[Integration_Result__c], 1, 0)
    VAR assetID = 
        SWITCH(TRUE(),
            pos1 > 0 && pos2 > 0, 
            MID(
                YourTable[Integration_Result__c],
                pos1 + LEN(search1),
                pos2 - pos1 - LEN(search1)
            )
        )
    RETURN assetID

This gives me the following output:

Is this what you were looking for?

[quincy_p]

Yes that's fantastic! Thank you!

[FreemanZ]

hi @quincy_p ,

not sure if i fully get you, it makes more sense to add a custom column in Power Query like:

Text.Select([result], {"1".."9"})

it works like: