Forum Discussion
Remove Blank Columns
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
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.
11 Replies
Anonymous , filter value <> null
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.
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.
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
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"
