removing rows with blank value on multiple columns

[selpaqm]

Hi,

ı have an huge table and I want to simplify it with removing blank rows. I have tried power query add custom column and conditional column methods and cannot succeed.

source file is csv on sharepoint and 3 column (want to remove blank on those) is text type

for an example;

id	unique	salesman	country	customer
1	123	aaa	gb	ada
2	2345
3	0938			awe
4	235		tr
5	545	ccc

as a result I want to remove rows that salesman and country and customer sections are empty in the same time. 

in this example 2nd row need to be removed. 

add custome column formula "=IF [Salesman] = "" and [country] ="" and [customer] ="" then 0 else 1 " not worked

"=IF [Salesman] = "" then 0 else if [country] ="" then 0 else if [customer] ="" then 0 else 1" not worked.

thanks

[Fowmy · Accepted solution]

@selpaqm 

In Power Query, Select All three Columns (salesman,country ,customer). 
On the Add Column Tab, Click Merge Column,
In your new Column, Filter out blank,

You get the desired results

You can delete the merged column

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[ArthurKvalheim]

When the dataset is reloaded with new data, would the filter then include new values that was not in the table before (will it still only exclude blank values)? Does any one know?

[ArthurKvalheim]

After clicking the conditions on the step, it seams indeed that it does only exclude the blanks (meaning it will include new values that will appear when reloading data later on). Great 🙂 :

[selpaqm]

thanks @Fowmy  . what a shame for me to not think about merge those cells.

Regards,

[Pragati11]

HI @selpaqm ,

I am assuming you have got blank values in all of the mentioned columns in source data and they have TEXT data-type.

Create a column as below using DAX expression:

IfBlankValue = IF ([Salesman] = BLANK() && [country] = BLANK() && [customer] = BLANK(), 0, 1)

Create a visual like the table you have shared in your screenshot. On this table visual, move IfBlankValue as a visual level filter and select value IfBlankValue = 1

Let me know if this approach works.

Thanks,

Pragati