conditionally duplicate column

[Anonymous]

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)

[Seth_C_Bauer · Accepted solution]

@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])

[Seth_C_Bauer · Accepted solution]

@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]

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.

[Seth_C_Bauer]

@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 · Accepted solution]

Hi @Anonymous,

---

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

---

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

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

Regards