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.
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?
Solved! Go to 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
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
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!
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |