Removing a large number of empty columns.

[Elihue]

Hello!

First time poster here. I am an excel novice. Long story short, I have a large .CSV spreadsheet where I need to automatically remove unused/blank columns. However the columns that end up empty will be different every time. I have tried using Power-Query to solve this issue but I only see a button for "remove blank rows" there does not seem to be a button for "remove blank columns". I tried "transforming" the data, then hitting the "remove blank rows" button and then "transforming" the data back. This works, but I lose my header names and I cannot have that. So I was wondering if anyone here could point me in the right direction of a quick and simple solution. Is there another power BI tool or piece of software that will support this function? Is there a way to do this in PQ that I'm just not seeing? By the way I am aware that columns can be removed manually but sometimes I end up with hundreds of different blank columns throughout my data that all need to be removed in a timely fashion. Any help would be greatly appreciated.

Thanks.

 

[Matt Allington]

I guess it is doable, but it will depend on your data. Depending on the specifics, this post may be similar

http://www.mrexcel.com/forum/power-bi/959791-merge-column-ignore-blanks-power-query.html

will the columns to be deleted always be 100% blanks?

maybe you could post a sample workbook.

 

[Elihue]

Hi Matt!

I seem to have found a solution to my problem. The data fields in the columns themselves would always be blank, but they would still have headers. For some reason, sense the columns had that little bit a header data none of the other easy methods for automatically removing blank columns that I found floating around the internet worked. They would see the header data and skip over all the blank columns thinking those columns were not blank. I finally found a solution! It's a random macro I found on a random website. I don't really know anything about macros because I am an excel novice. But here it is so you guys can check it out.

<code>Sub DeleteEmptyColumns()

Dim lastColumn As Integer
Dim lastRow As Integer
Dim i As Integer
Dim r As Range

Set r = ActiveSheet.Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious)

If Not r Is Nothing Then

lastColumn = r.Column

For i = lastColumn To 1 Step -1

Set r = ActiveSheet.Columns(i).Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious)

If r Is Nothing Then
ActiveSheet.Columns(i).Delete
Else
If r.Row = 1 Then
ActiveSheet.Columns(i).Delete
End If
End If

Next i

End If

End Sub

So if your like me and have large .CSV spreadsheets with random columns that have headers, but otherwise are blank, this might work for you. Now I just need to figure out PowerPivot. So I can make the data as easy to read as possible with pivot tables and slicers.
</code>

 

[Matt Allington]

Good luck. If you want a quick path to success with power pivot, read my book - you won't be sorry. Http://xbi.com.au/learndax