cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jahida Impactful Individual
Impactful Individual

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

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.

rliu Helper I
Helper I

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

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

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

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

rliu Helper I
Helper I

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

@jahida Alright. Thanks!

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.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors