Combine 2 tables with empty cells

Question

The problem below occurs:We have 2 database files. The first contains the number of hours per file number at activity level. In the second file you have the revenue amount per file number at activity level.We want an overview of the number of hours spent and revenue made per file number per activity. But the activities do not have the same names and not every activity can be used for both the hours file and the turnover file.Simplified example&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Database for the hours&nbsp;&nbsp;&nbsp;&nbsp;Database for the revenue&nbsp;ActivityFile numberWorked ours&nbsp;&nbsp;ActivityFile numberRevenue&nbsp;AAC12340,250&nbsp;&nbsp;XYB1234&nbsp;€&nbsp; 150,00&nbsp;AAD12340,125&nbsp;&nbsp;XYD1235&nbsp;€&nbsp; 200,00&nbsp;AAA12350,500&nbsp;&nbsp;XYZ1236&nbsp;€&nbsp;&nbsp;&nbsp; 25,00&nbsp;AAA12361,000&nbsp;&nbsp;XYZ1236&nbsp;€&nbsp;&nbsp;&nbsp; 75,00&nbsp;AAB12360,250&nbsp;&nbsp;XYA1237&nbsp;€&nbsp; 125,00&nbsp;AAC12360,125&nbsp;&nbsp;XYD1237&nbsp;€&nbsp; 175,00&nbsp;AAD12360,500&nbsp;&nbsp;XYC1237&nbsp;€&nbsp;&nbsp;&nbsp; 50,00&nbsp;AAC12371,000&nbsp;&nbsp;XYB1238&nbsp;€&nbsp;&nbsp;&nbsp; 25,00&nbsp;AAD12370,250&nbsp;&nbsp;XYA1238&nbsp;€&nbsp; 100,00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;So some activities are linked and others not. Like in the example below&nbsp;&nbsp;&nbsp;&nbsp;Linking table&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Activity hoursActivity revenue&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AAAXYZ&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AABXYA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AAC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;XYD&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AADXYC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;XYB&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Now we want to see when the file number are the same if there are hours against the made revenue.&nbsp;File numberActivity revenueActivity hoursamountours&nbsp;&nbsp;&nbsp;&nbsp;1234XYZAAA&nbsp;€&nbsp;&nbsp;&nbsp; 50,000,500&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;XYAAAB&nbsp;€&nbsp; 100,001,000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;XYB&nbsp;&nbsp;€&nbsp; 150,000,000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AAC&nbsp;€&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;0,250&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AAD&nbsp;€&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;0,125&nbsp;&nbsp;&nbsp;&nbsp;1235XYD&nbsp;&nbsp;€&nbsp; 200,000,000&nbsp;&nbsp;&nbsp;&nbsp;1236XYZ&nbsp;AAA&nbsp;&nbsp;€&nbsp; 100,001,000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AAB&nbsp;€&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;0,250&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AAC&nbsp;€&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;0,125&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AAD&nbsp;€&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;0,500&nbsp;&nbsp;&nbsp;&nbsp;1237XYA&nbsp;&nbsp;€&nbsp; 125,000,000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;XYD&nbsp;&nbsp;€&nbsp; 175,000,000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;XYCAAD&nbsp;€&nbsp;&nbsp;&nbsp; 50,000,250&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AAC&nbsp;€&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;1,000&nbsp;&nbsp;&nbsp;&nbsp;1238XYB&nbsp;&nbsp;€&nbsp;&nbsp;&nbsp; 25,000,000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;XYA&nbsp;&nbsp;€&nbsp; 100,000,000&nbsp;&nbsp;&nbsp;&nbsp;

v-robertq-msft · Answer

Hi, Svenvdh&nbsp;
According to your description and sample data, I can’t clearly understand what you want to get. Would you like to explain the columns in your expected output in detail(using some examples to explain why they are here and why their value is like this)?

&nbsp;
Thanks very much!
How to Get Your Question Answered Quickly&nbsp;
&nbsp;
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Combine 2 tables with empty cells

1 Reply

Related content

Tables combine

Combine columns if not null or empty

Table visual is empty

empty table values

Combining three CONCATENATEX Measures with results from two Tables

Simplified example

Database for the hours					Database for the revenue
Activity	File number	Worked ours			Activity	File number	Revenue
AAC	1234	0,250			XYB	1234	€ 150,00
AAD	1234	0,125			XYD	1235	€ 200,00
AAA	1235	0,500			XYZ	1236	€ 25,00
AAA	1236	1,000			XYZ	1236	€ 75,00
AAB	1236	0,250			XYA	1237	€ 125,00
AAC	1236	0,125			XYD	1237	€ 175,00
AAD	1236	0,500			XYC	1237	€ 50,00
AAC	1237	1,000			XYB	1238	€ 25,00
AAD	1237	0,250			XYA	1238	€ 100,00

So some activities are linked and others not. Like in the example below
Linking table
Activity hours	Activity revenue
AAA	XYZ
AAB	XYA
AAC
	XYD
AAD	XYC
	XYB

Now we want to see when the file number are the same if there are hours against the made revenue.
File number	Activity revenue	Activity hours	amount	ours
1234	XYZ	AAA	€ 50,00	0,500
	XYA	AAB	€ 100,00	1,000
	XYB		€ 150,00	0,000
		AAC	€ -	0,250
		AAD	€ -	0,125
1235	XYD		€ 200,00	0,000
1236	XYZ	AAA	€ 100,00	1,000
		AAB	€ -	0,250
		AAC	€ -	0,125
		AAD	€ -	0,500
1237	XYA		€ 125,00	0,000
	XYD		€ 175,00	0,000
	XYC	AAD	€ 50,00	0,250
		AAC	€ -	1,000
1238	XYB		€ 25,00	0,000
	XYA		€ 100,00	0,000