Forum Discussion
removing rows with blank value on multiple columns
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
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 🙂
5 Replies
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 🙂
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?
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 🙂 :
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
