Calculated Table Returns a Blank Row

[jmeccles]

Hey, everyone.

I created a table with no blank rows that looks like this:

Table 1

Branch	A	B	Year	Key
KGN	10	9	2017	10KGN2017
MBY	10	4	2017	10MBY2017
PMR	10	1	2017	10PMR2017
SAV	10	9	2017	10SAV2017
KGN	11	0	2017	11KGN2017
MBY	11	0	2017	11MBY2017
PMR	11	0	2017	11PMR2017
SAV	11	0	2017	11SAV2017
KGN	12	0	2017	12KGN2017
MBY	12	0	2017	12MBY2017
PMR	12	0	2017	12PMR2017
SAV	12	0	2017	12SAV2017
KGN	1	366	2017	1KGN2017
MBY	1	132	2017	1MBY2017
PMR	1	101	2017	1PMR2017
SAV	1	228	2017	1SAV2017
KGN	2	174	2017	2KGN2017
MBY	2	96	2017	2MBY2017
PMR	2	88	2017	2PMR2017
SAV	2	213	2017	2SAV2017
KGN	3	207	2017	3KGN2017
MBY	3	134	2017	3MBY2017
PMR	3	128	2017	3PMR2017
SAV	3	227	2017	3SAV2017
KGN	4	209	2017	4KGN2017
MBY	4	115	2017	4MBY2017
PMR	4	116	2017	4PMR2017
SAV	4	228	2017	4SAV2017
KGN	5	194	2017	5KGN2017
MBY	5	129	2017	5MBY2017
PMR	5	83	2017	5PMR2017
SAV	5	223	2017	5SAV2017
KGN	6	173	2017	6KGN2017
MBY	6	143	2017	6MBY2017
PMR	6	116	2017	6PMR2017
SAV	6	249	2017	6SAV2017
KGN	7	211	2017	7KGN2017
MBY	7	146	2017	7MBY2017
PMR	7	127	2017	7PMR2017
SAV	7	245	2017	7SAV2017
KGN	8	184	2017	8KGN2017
MBY	8	152	2017	8MBY2017
PMR	8	137	2017	8PMR2017
SAV	8	268	2017	8SAV2017
KGN	9	60	2017	9KGN2017
MBY	9	30	2017	9MBY2017
PMR	9	32	2017	9PMR2017
SAV	9	77	2017	9SAV2017

Then I created a calculated table using the following formula:
Table 2 = ALLEXCEPT( 'Table 1', 'Table1'[Branch], 'Table 1'[A] )

Unexpectedly, it returned a table with an extra row which is completely blank - i.e.:

Table 2

B	Year	Key
366	2017	1KGN2017
174	2017	2KGN2017
207	2017	3KGN2017
209	2017	4KGN2017
194	2017	5KGN2017
173	2017	6KGN2017
211	2017	7KGN2017
184	2017	8KGN2017
60	2017	9KGN2017
9	2017	10KGN2017
0	2017	11KGN2017
0	2017	12KGN2017
101	2017	1PMR2017
88	2017	2PMR2017
128	2017	3PMR2017
116	2017	4PMR2017
83	2017	5PMR2017
116	2017	6PMR2017
127	2017	7PMR2017
137	2017	8PMR2017
32	2017	9PMR2017
1	2017	10PMR2017
0	2017	11PMR2017
0	2017	12PMR2017
132	2017	1MBY2017
96	2017	2MBY2017
134	2017	3MBY2017
115	2017	4MBY2017
129	2017	5MBY2017
143	2017	6MBY2017
146	2017	7MBY2017
152	2017	8MBY2017
30	2017	9MBY2017
4	2017	10MBY2017
0	2017	11MBY2017
0	2017	12MBY2017
228	2017	1SAV2017
213	2017	2SAV2017
227	2017	3SAV2017
228	2017	4SAV2017
223	2017	5SAV2017
249	2017	6SAV2017
245	2017	7SAV2017
268	2017	8SAV2017
77	2017	9SAV2017
9	2017	10SAV2017
0	2017	11SAV2017
0	2017	12SAV2017
null	null	null

How can I get rid of this blank row? Is it even possible?

[v-huizhn-msft · Accepted solution]

Hi @jmeccles,

>>Then I created a calculated column using the following formula:Table 2 = ALLEXCEPT( 'Table 1', 'Table1'[Branch], 'Table 1'[A] )

The ALLEXCEPT function can not be used as a calculated column, you created a new table using the formula rather than calculated column by clicking "New Table" under Modeling on Home page.

Please create a new table using the formula below, you will remove the null rows.

Table 2 = FILTER( ALLEXCEPT(Table1,Table1[Branch],Table1[A]),Table1[B]<>BLANK()&&Table1[Year]<>BLANK()&&Table1[Key]<>BLANK())

Best Regards,
Angelia

[jmeccles]

Hi there, @v-huizhn-msft!

That was a mistake - I meant that I created a calculated "table", not "column".

Your solution works for me. Thanks!