PowerQuery remove Columns


Hi Guys


is there an uncomplicated way to remove an "unknown number" of columns? for example, all what start with "column.01, column.02, column.03" and so on for example with column.0*


1. i need to remove approx. 400 columns

2. it can be that there are more than the 400 columns 


2 Replies


Sub columnsdelete()

Dim i As Long

For i = 1 To 9

If Cells(1, i).Value Like "column.0*" Then
i = i - 1

End If

Next i

End Sub

An alternative could be this code. In the attached sample file you can click the button in cell F2 to run the macro. The code can easily be adapted to other ranges.



You need to create list of columns to remove. That could be done by different ways and depends on logic in column names. As variant

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    columnsToRemove = List.Select( Table.ColumnNames(Source), each Text.StartsWith( _, "Column.0") ),
    removeColumns = Table.RemoveColumns(Source, columnsToRemove)