cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rliu
Helper I
Helper I

How to remove columns with NA or columns of a certain type in power bi?

I have a csv file which has 100 columns, corresponding to 100 countries' currency value. Some of colomns are numerical but some of them are text "N/A". Is there a filter that allows me to filter out the countries with NAs, rather than doing it one by one manually?

1 ACCEPTED SOLUTION

Under the Query Editor > Choose the column > Text Filters

Or "Replace Values".

 

Hope that's what you're looking for.

 

ReplaceValues.PNGTextFilters.PNG

 

 

Kris

View solution in original post

13 REPLIES 13
a_mixed_life
Resolver I
Resolver I

If this is coming from a CSV file, couldn't you just create an excel formula "IF" and then replacing it with a dash ( - )? Then when PowerBI queries that csv file, it'll come in as a dash and filter from that.

Kris

Thanks! How to filter columns in power bi though? 

When I say "Replace Values", you may be able to replace N/A into a dash which is cleaner that it saying "#N/A".

Kris

Under the Query Editor > Choose the column > Text Filters

Or "Replace Values".

 

Hope that's what you're looking for.

 

ReplaceValues.PNGTextFilters.PNG

 

 

Kris

View solution in original post

Thanks kris. However there is no text filter under my choosing columns. Did I used the wrong icon?colomns.PNG

v-qiuyu-msft
Community Support
Community Support

Hi @a_mixed_life,

 

Based on the data type of the column field, you can see different filters like Text Filters and Number Filters. To see this kind of feature, you can click the inverted triangle next to the column header, then you can see Text Filters.

 

q1.PNG

 

If you have any question, please feel free to ask.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-qiuyu-msft,

 

I see what you are saying. It's good to know. Thank you!

 

However my question was not filter certain rows in a chosen column. So the senario is a csv withwhere some columns have  NA in every rows, and my task is to remove all the columns like that. Did I make myself clear?

jahida
Impactful Individual
Impactful Individual

As I said before, I don't think PowerBI has a simple way to do batch column processing like that; it's not a very common operation I think. I still think pivoting might be the optimal method, but if you don't like that, you could try using the Table.Transpose function:

 

https://msdn.microsoft.com/en-us/library/mt260836.aspx

 

Basically I'm thinking you could transpose, filter out rows with errors, and then transpose again to get back to where you started. You'll probably want to use Table.DemoteHeaders before the first transpose (as in the example), and Table.PromoteHeaders after the second one.

 

You can use this part of the wizard to remove errors:

 

Capture.PNG

 

Did some testing on my local on a dummy dataset and I'm pretty sure this will suit your needs.

Hi @jahida,

 

Thank you for your answer. So I tried pivoting: unpivot-filter out NAs- pivot back. However when trying to pivot back, I ran into problems. The steps are shown below. Thank you for looking into this.

5.PNG

2.PNG1.PNG3.PNG4.PNG

 

 So I thought I already filtered out the N/As. Why it's still saying that?

jahida
Impactful Individual
Impactful Individual

Late reply, but I have no idea what's causing that error... try the second method I outlined (using Transpose)?

@jahida Alright. Thanks!

Vvelarde
Community Champion
Community Champion

In Edit Query (Query Editor)

 

in Edit queryin Edit query




Lima - Peru
jahida
Impactful Individual
Impactful Individual

This sounds like a case where unpivoting first might be optimal. Then you can filter the rows as desired. I don't think PowerBI has that kind of aggregated column processing, but I could be wrong.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors