Forum Discussion

KevinMorneault's avatar
KevinMorneault
Icon for Helper II rankHelper II
4 years ago
Solved

How to copy a specific string from a column to another

Hi All I would like to know if this can be done.  I have a column which has text (Summary). I would like to know if i can add a column(ExpenseCode) and copy only some part of the needed from(Summary...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    4 years ago

    Ah, interesting.  Yes, that does change things a bit.  I am going to assume you want the three values in three different columns: Expense Code, GL Code, Invoice Number.  
    We add three calculated columns, each set up to look for a different string and pull back all the text after that string up to the next carrige return (UNICHAR(10)) like so:

    Expense Code = 
VAR _Code = "Expense Code : "
VAR _CodeLen = LEN ( _Code )
VAR _CodeMissing = ISERROR ( FIND ( _Code, 'DataTable'[Column] ) )
VAR _CodePosition = IF ( _CodeMissing, BLANK (), FIND ( _Code, 'DataTable'[Column] ) + _CodeLen )
VAR _CodeEnd = IF ( _CodeMissing, BLANK (), FIND ( UNICHAR ( 10 ), 'DataTable'[Column], _CodePosition ) )
RETURN
    IF ( _CodeMissing, BLANK (), MID ( 'DataTable'[Column], _CodePosition, _CodeEnd - _CodePosition ) )
    GL Code = 
VAR _Code = "GL Code : "
VAR _CodeLen = LEN ( _Code )
VAR _CodeMissing = ISERROR ( FIND ( _Code, 'DataTable'[Column] ) )
VAR _CodePosition = IF ( _CodeMissing, BLANK (), FIND ( _Code, 'DataTable'[Column] ) + _CodeLen )
VAR _CodeEnd = IF ( _CodeMissing, BLANK (), FIND ( UNICHAR ( 10 ), 'DataTable'[Column], _CodePosition ) )
RETURN
    IF ( _CodeMissing, BLANK (), MID ( 'DataTable'[Column], _CodePosition, _CodeEnd - _CodePosition ) )
    Invoice Number = 
VAR _Code = "Invoice Number : "
VAR _CodeLen = LEN ( _Code )
VAR _CodeMissing = ISERROR ( FIND ( _Code, 'DataTable'[Column] ) )
VAR _CodePosition = IF ( _CodeMissing, BLANK (), FIND ( _Code, 'DataTable'[Column] ) + _CodeLen )
VAR _CodeEnd = IF ( _CodeMissing, BLANK (), FIND ( UNICHAR ( 10 ), 'DataTable'[Column], _CodePosition ) )
RETURN
    IF ( _CodeMissing, BLANK (), MID ( 'DataTable'[Column], _CodePosition, _CodeEnd - _CodePosition ) )

    My sample data has 3 rows that look like this.  You can see that the first entry only had the Expense Code so that is the only one that comes back.

    Joh Doe
    IT Equipment Request
    Expense Code : 1234
    Device: Laptop
    Model: Latitude
    Cost: 1K
    this system needs to be install with windows 11 and other application
    please verify with the user ...... etc..
    Joh Doe
    IT Equipment Request
    Expense Code : 6654654654654
    GL Code : 987654
    Invoice Number : 789321654798
    Device: Laptop
    Model: Latitude
    Cost: 1K
    this system needs to be install with windows 11 and other application
    please verify with the user ...... etc..
    Joh Doe
    IT Equipment Request
    Expense Code : 1455654
    GL Code : 987654
    Invoice Number :
    Device: Laptop
    Model: Latitude
    Cost: 1K
    this system needs to be install with windows 11 and other application
    please verify with the user ...... etc..

     

     

  • jdbuchanan71's avatar
    jdbuchanan71
    4 years ago

    It looks like you need to adjust the _Code VAR to match your real data.

    Expense Code = 
VAR _Code = "Expense Code : "

    This first VAR is set to the string "Expense Code : ",  this is the string it is searching for in the data but in your data it looks like the string is "ExpenseCode: " so you would have to change the measure to match.

    Expense Code = 
VAR _Code = "ExpenseCode: "
VAR _CodeLen = LEN ( _Code )
VAR _CodeMissing = ISERROR ( FIND ( _Code, 'DataTable'[Column] ) )
VAR _CodePosition = IF ( _CodeMissing, BLANK (), FIND ( _Code, 'DataTable'[Column] ) + _CodeLen )
VAR _CodeEnd = IF ( _CodeMissing, BLANK (), FIND ( UNICHAR ( 10 ), 'DataTable'[Column], _CodePosition ) )
RETURN
    IF ( _CodeMissing, BLANK (), MID ( 'DataTable'[Column], _CodePosition, _CodeEnd - _CodePosition ) )
jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
4 years ago

Change the string it is searching for.  Instead of searching for "Expense Code : " search for "ExpenseCode: ExpenseCode "

Yes, even include the space at the end so the formula can find the exact position where the actual code starts.

Expense Code = 
VAR _Code = "ExpenseCode:  ExpenseCode "
VAR _CodeLen = LEN ( _Code )
VAR _CodeMissing = ISERROR ( FIND ( _Code, 'DataTable'[Column] ) )
VAR _CodePosition = IF ( _CodeMissing, BLANK (), FIND ( _Code, 'DataTable'[Column] ) + _CodeLen )
VAR _CodeEnd = IF ( _CodeMissing, BLANK (), FIND ( UNICHAR ( 10 ), 'DataTable'[Column], _CodePosition ) )
RETURN
    IF ( _CodeMissing, BLANK (), MID ( 'DataTable'[Column], _CodePosition, _CodeEnd - _CodePosition ) )

The rest of the code will take care of removing the extra characters.  That is what VAR _CodePosition and VAR _CodeEnd do.