Need help in removing blank rows

[Anonymous]

Hi

I have raw data table in which there are about 4 rows that i want to remove which are blank in a particular column. I am choosing the column > Transform Data > Home > Remove Rows > Remove Blank Rows... but this is not working. What am i doing wrong? Also, i dont mind leaving them but how do i uncheck (blank) from the visual?

[AllisonKennedy · Accepted solution]

Click on the View tab in Power Query editor and tick the box to turn on the Formula bar so you can see what the code is that happens when you click the buttons. I believe remove blank rows only removes rows that are entirely blank, while remove errors removes based on selected column. To remove blanks from a specific column, just use the arrow on the column heading to filter the column and untick blank/null values.

[AllisonKennedy · Accepted solution]

Click on the View tab in Power Query editor and tick the box to turn on the Formula bar so you can see what the code is that happens when you click the buttons. I believe remove blank rows only removes rows that are entirely blank, while remove errors removes based on selected column. To remove blanks from a specific column, just use the arrow on the column heading to filter the column and untick blank/null values.

[Anonymous]

Hi

I ran into a problem, am trying to build a connection with the same table. So i have the raw data table in which i have country column which has duplicates so am uploading a file with the list of the unique countries. when i connect the both, the (blank) re appears, why? I have applied filter (Blank) to that column and the Unique Country List table does not have any blank.

[AllisonKennedy]

Do you mean the blank appears in a chart or other visualization? This is usually because the country data doesn't have an exact match so blanks will appear in the visualization.

[Anonymous]

It appears in the visual as (blank) with values 0, the country list is exactly the same but just doesnt include duplicates.

[AllisonKennedy]

I am assuming that you have the following setup:
DimCountry table with [Country] column with unique values.
Fact table with [Country] column with duplicates and [Value] column.

Visual (let's use matrix for this example) with DimCountry[Country] in Rows and Fact[Value] in Values.

Add Fact[Country] to the rows under DimCountry[Country] and expand the blank section to see which countries appear. Those are the ones without the match - maybe there's an added space or difference in capital and lowercase or something similar?

[Anonymous]

You are exactly right and those are the countries i want to exclude as they will not have any values ever. that was the whole idea of having a separate table wxluding these countries so i can choose DimCountry for visuals

[Anonymous]

Sorry, you are right, the raw data includes some countries to which i will never receive any data, hence want to exclude. Is this the reason?

[AllisonKennedy · Accepted solution]

Yes, so since those countries are excluded from your Country table, they will appear as Blank in the visual. You can just filter the visual to exclude blank in this case, or you will need to apply additional filter in the raw data to remove them from all tables.

[Anonymous]

Got it! Thanks so much

[Tahreem24]

You are going in the right direction.  Just do the little workaround if you are not getting from your action.

Go to transform query editor  > select the column on which you wanna remove blank values then click on the small down arrow icon to get the list of values that are available on this column>  uncheck the "null" value > Save and apply this changes.

[Anonymous]

Thanks!