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?
Solved! Go to Solution.
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.
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.
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?
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:
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.
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?
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.
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps