Forum Discussion
New column using three other columns - Two being blank
Hi,
I am trying to add a new column (Reporting Qty TO) which returns the value from Quoted Qty where the other two columns are BLANK, however in this same new column (Reporting Qty TO) if there is a vlaue in either Reporting Qty RTU or Reporting Qty Sample I would like that to value to be entered into new column (Reporting Qty TO).
The existing columns I have are:
Quoted Qty (Has a value for every row)
Reporting Qty RTU (Only has a value for specific rows)
Reporting Qty Sample (Only has a value for specific rows)
Any help is really appreicated 🙂
You can create a calculated column like
Reporting Qty TO = COALESCE ( 'Table'[Reporting Qty RTU], 'Table'[Reporting Qty Sample], 'Table'[Quoted Qty] )The order you put the columns in matters - it will use the first non-blank value it finds, so in this example if Reporting Qty RTU has a value then it will use that. Only if that column is blank will it check Reporting Qty Sample.
2 Replies
johnt75Super User
You can create a calculated column like
Reporting Qty TO = COALESCE ( 'Table'[Reporting Qty RTU], 'Table'[Reporting Qty Sample], 'Table'[Quoted Qty] )The order you put the columns in matters - it will use the first non-blank value it finds, so in this example if Reporting Qty RTU has a value then it will use that. Only if that column is blank will it check Reporting Qty Sample.