Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to 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])
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
@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])
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 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"
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |