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 number. Some Customers are part of the same group (related customers). They each have a unique customer number, but they all have the same group number.

 

A Customer group consist of one or more customers.

 

In the data set we receive, all 3 columns have a value if the customer is part of a group of 2 or more customers. However, if the customer has no related customers, the field Customer Group is empty.

 

We would like to create an additional column, whereby we populate that column with the Customer Group Number, and in cases where the Customer has no related customers (meaning the value in the column 'Customer Group" is blank), we would like to copy the Customer number into that column.

 

Is that possible?

 

What we try to achieve is to create a column where all customers have a Group Number (even the ones that do not have a related customer, in which case their group number will be their own Customer Number)

  • 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

4 Replies

  • 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"