Merge data from two columns into one

[zirloko]

Background: Our transactions are booked using either project codes or cost center (CC) (not both). Ultimately the project codes roll to specific CC. So I want to be able to show a total spend by CC regardless of it's project or CC based. The best I can do with a measure is to show results booked directly to a CC, and the next column over show results with a derived CC from the project. How can I get it into one?

The best I can do is this result. How can I get this back in one CC column? The 75 and 225 represents the spend directly to projects, but I'd like the derived CC to be in-line with the CC. 

CC	Amount	Derived CC
	75	4707
	225	4708
4707	100
4708	35
4709	40
	475

My sample dataset:

Date           Account   CC              Project              Amount  Text 

1/1/2019	642801	4707		100	Entry 1 of 7
1/15/2019	642901		DD-4-11-0006	50	Entry 2 of 7
1/25/2019	642703		DD-4-01-0018	25	Entry 3 of 7
2/1/2019	643303	4708		35	Entry 4 of 7
3/5/2019	643701	4709		40	Entry 5 of 7
3/15/2019	642801		MG-4-01-0001-01	150	Entry 6 of 7
3/31/2019	642801		MG-4-01-0001-01-01	75	Entry 7 of 7

                                                                                 475

Mapping of projects to CC. Then this table is linked to my cost center look-up table on the CC number column.

Dimension         Project Number    Description                         CC

Project	DD-4-01	X1		4707
Project	DD-4-02	X2		4707
Project	MG-4-01	X3		4708

Thanks!

[Anonymous · Accepted solution]

In the query editor try adding a conditional column. In the dialog, IF CC > 0 Then CC Else Select a Column -> Derived CC