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

KevinMorneault 

You can add the column like this.

Expense Code =
IF (
    CONTAINSSTRING ( YourTable[Column 1  (Summary)], "Expense Code" ),
    SUBSTITUTE ( YourTable[Column 1  (Summary)], "Expense Code : ", "" )
)

 

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

Hi 

Tks i have tried it but it copy the everything from the column. I know why so here's what happen

 

the Line Expense Code:  has more info within the same line so heres an example of the line, cannot really copy and past the whole thing due to sensitive information..

 

--- Approval Information ---
Department: Department Name
Organization: Org Name
ExpenseCode, GL or Invoice: Expense Code (Division1) Expense Code: 12345678.98877.776655.444
GL Code:
COA Approver: approver email

 

So some scenario it would be Expense code and other would be GL as we have 2 Divsion.

right now it just copy everything to the new column. 

 

What you provided is exactly what i am looking for.

 

 

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

    One thing i forgot to mention is this data is all within one field. so basically it's a form they fill out and the data is being dump in 1 field. within the specific colum.