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

Accepted Solutions
a_mixed_life Resolver I
Resolver I

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

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
jahida Impactful Individual
Impactful Individual

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

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.

a_mixed_life Resolver I
Resolver I

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

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
rliu Helper I
Helper I

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

Thanks! How to filter columns in power bi though? 

Vvelarde Community Champion
Community Champion

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

In Edit Query (Query Editor)

 

Filter.pngin Edit query




Lima - Peru
a_mixed_life Resolver I
Resolver I

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

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

a_mixed_life Resolver I
Resolver I

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

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
rliu Helper I
Helper I

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

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

Moderator v-qiuyu-msft
Moderator

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

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.
rliu Helper I
Helper I

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

@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?

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors