Remove Blank Columns

[Anonymous]

Hello

How can I remove blank columns in Power BI Power Query Editor.

I have a report that returns columns with nothing in.  I can't select columns and remove selected, as future runs of the source report may have data in them and so will want those columns to remain.

I believe it needs to be done via the Advanced Editor Applied Steps, but, I'm not sure how.

Thank you

Ian

[mahoneypat]

You should remove any columns that will never have data, but can you clarify why you want to remove columns that sometimes have data? They would take up barely any file size when empty, and you could handle the nulls in your measures if necessary. What issue are you seeing from sometimes-empty columns?

Pat

[Anonymous]

Hi mahoneypat.

I have 10 tables.  When the reports are run in eFront to create the reports, for some reason there are over 100 columns in each table that have Column names like Column 100 up to Column 222 as well as the valid Columns which I require.

When in Power BI I select the Field List in a Table for any of these tables, I am greeted with the columns in alpha order, but my view of them is being hampered by all of these empty columns.  It's a "I can't see the wood for the trees problem"

[Adescrit]

In Power Query you can Transpose your table. Then if you filter out / remove blank rows (these were columns prior to the transpose step) before transposing once more to return your table to its original layout.

[Anonymous]

Hi Adescrit

Thank you

Is this the Transform/Unpivot Columns/Unpivot Columns?

Then would I follow up with Remove Blank Rows, followed by Transform/Unpivot Columns/Unpivot Columns?

Please can you confirm my understanding?

Thank you

[Adescrit · Accepted solution]

In Power Query, go to the Transform tab. Then select Transpose (Top-left of screen)

Then back to the Home tab and select Remove Rows > Remove Blank Rows (these blank rows were your blank columns prior to transposing)

Finally Transform > Transpose once more.

[Anonymous]

Hi Adescrit

Thank you, but...

When I Transpose back to the original format - I have lost all of my column names.

Should I do this step directly after the Navigation Step?

[Syndicate_Admin]

Yes, I don't see a way not to lose them because if you first go from headers to the first row, when you transpose the first column it is not blank (they are the old headers) and the delete blank rows does not find any.

[Adescrit]

Yes might be best to try this before the "Use First Row As Headers" step

[Anonymous]

Many thanks for your help.

I tried it on a copy of the file, whilst awaiting your reply.

Thank you for your rapid responses.

It works prefectly !! 😊

[amitchandak]

@Anonymous , filter value <>  null

[Anonymous]

Hi amitchandak

Thank you, but...

Is this an Applied Step or an option in Power Query Editor?

Will this filter out any blank cell or only columns that have no data with the exception of a Column name?