PowerQuery remove Columns

Contributor

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

@PaddyB 

Sub columnsdelete()

Dim i As Long

For i = 1 To 9

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

Else
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.

 

@PaddyB 

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

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