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

[rliu]

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?

[a_mixed_life]

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.

[rliu]

Thanks! How to filter columns in power bi though? 

[a_mixed_life]

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

[a_mixed_life · Accepted solution]

Under the Query Editor > Choose the column > Text Filters

Or "Replace Values".

Hope that's what you're looking for.

[rliu]

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

[v-qiuyu-msft]

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.

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

Best Regards,
Qiuyun Yu

[rliu]

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

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:

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

[rliu]

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.

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

[jahida]

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

[rliu]

@jahida Alright. Thanks!

[Vvelarde]

In Edit Query (Query Editor)

in Edit query

[jahida]

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.