Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

conditionally duplicate column

We have a data set in Excel with customer data. The set has a few columns:   Customer Name Customer Number Customer Group   Each Customer (=each row in the Excel file) has a unique Customer num...
  • Anonymous's avatar
    Anonymous
    9 years ago

    Anonymous You can create a calculated column to do this. It would look something like this (Substitute your actual table and field names.

     

    NewGroup = IF('Table1'[group] = BLANK(), 'Table1'[Num], 'Table1'[group])

  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Anonymous,

    = IF('Combined PDAR'[Connection ID] = BLANK(), 'Combined PDAR'{Entity ID],'Combined PDAR'[Connection ID])

    In addition, also change "{" to "[" in the formula.:smileyhappy:

    =
IF (
    'Combined PDAR'[Connection ID] = BLANK (),
    'Combined PDAR'[Entity ID],
    'Combined PDAR'[Connection ID]
)

     

    Regards

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Anonymous You can create a calculated column to do this. It would look something like this (Substitute your actual table and field names.

 

NewGroup = IF('Table1'[group] = BLANK(), 'Table1'[Num], 'Table1'[group])

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Thanks !

     

    The Table is called 'Combined PDAR'

    In that table we have a field with the Customer Number called 'Entity ID'

    And a field with the Customer Group number called 'Connected ID'

     

    So I duplicated the column 'Connection ID' (since I do not want to loose the original data) and then added a custom formula:

     

    = IF('Combined PDAR'[Connection ID] = BLANK(), 'Combined PDAR'{Entity ID])

     

    I get the following error:

    Expression.SyntaxError: Token Literal expected.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Anonymous You are missing the final part of the formula

      = IF('Combined PDAR'[Connection ID] = BLANK(), 'Combined PDAR'{Entity ID],'Combined PDAR'[Connection ID])

       

      essentially you are saying: If the "Connection ID" is blank, use the "Entity ID", otherwise use the "Connection ID"

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi Anonymous,

        = IF('Combined PDAR'[Connection ID] = BLANK(), 'Combined PDAR'{Entity ID],'Combined PDAR'[Connection ID])

        In addition, also change "{" to "[" in the formula.:smileyhappy:

        =
IF (
    'Combined PDAR'[Connection ID] = BLANK (),
    'Combined PDAR'[Entity ID],
    'Combined PDAR'[Connection ID]
)

         

        Regards