Forum Discussion

quincy_p's avatar
quincy_p
Advocate I
2 years ago
Solved

Create New Column based on Value found between Quotation Marks

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's avatar
    EylesIT
    2 years ago

    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?

3 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    2 years ago

    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:

  • EylesIT's avatar
    EylesIT
    Resolver II
    2 years ago

    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?