Search code examples
powerquerym

Remove all columns to the right of a specific column

I have an Excel template file with a dynamic number of columns that represent work week dates. Some users have decided to add their own subtotal columns to the right of those columns. I need a way to identify the first blank column, and then truncate that column and all columns following it.

I had previously been using the following script to remove all columns that begin with the word "Column":

// Create a list of columns that start with "Column" and remove them.
Removed_ColumnNum_Columns = Table.RemoveColumns(PreviousStepName, List.Select(Table.ColumnNames(PreviousStepName), each Text.StartsWith(_, "Column") )),

Based on being able to find the first ColumnXX column, I want to remove it and all columns after it

enter image description here

Solution

  • You can use List.PositionOf to get your ColumnIndex instead of parsing text.

    I'd put it together like this:

    // [...]
ColumnList = Table.ColumnNames(#"Promoted Headers"),
ColumnXX = List.Select(ColumnList, each Text.StartsWith(_, "Column")){0}, 
ColumnIndex = List.PositionOf(ColumnList, ColumnXX),
ColumnsToKeep = List.FirstN(ColumnList, ColumnIndex),
FinalTable = Table.SelectColumns(#"Promoted Headers", ColumnsToKeep)