Answered by:
Remove columns based on columns names in Power Query

Question
-
Hello,
In Power Query, how can I remove columns based on how their names begin?
i.e. I would like to remove all columns that begin with the prefix _ABC, no matter how many there are ...the number of columns may change across files...
Thank you,
Tamir
Thursday, October 25, 2018 3:57 AM
Answers
-
Another possibility would be:
RemovedColumnList = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "_ABC")),
RemovedColumns = Table.RemoveColumns(Source, RemovedColumnList)
- Marked as answer by Tamiri7 Thursday, October 25, 2018 3:58 PM
Thursday, October 25, 2018 3:53 PM -
Hi
Code edited: Added if List.IsEmpty… in case there would be no column names start with "_ABC"
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ColStartWith = "_ABC",
tblColumnNames = Table.FromList(Table.ColumnNames(Source), Splitter.SplitByNothing(), {"myColumnName"}, null, ExtraValues.Error),
ColToRemoveAsList = Table.SelectRows(tblColumnNames, each Text.StartsWith([myColumnName], ColStartWith))[myColumnName],
RemoveColumns = if List.IsEmpty(ColToRemoveAsList) then Source else Table.RemoveColumns(Source, ColToRemoveAsList)
in
RemoveColumns
Thursday, October 25, 2018 4:53 AM
All replies
-
Hi
Code edited: Added if List.IsEmpty… in case there would be no column names start with "_ABC"
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ColStartWith = "_ABC",
tblColumnNames = Table.FromList(Table.ColumnNames(Source), Splitter.SplitByNothing(), {"myColumnName"}, null, ExtraValues.Error),
ColToRemoveAsList = Table.SelectRows(tblColumnNames, each Text.StartsWith([myColumnName], ColStartWith))[myColumnName],
RemoveColumns = if List.IsEmpty(ColToRemoveAsList) then Source else Table.RemoveColumns(Source, ColToRemoveAsList)
in
RemoveColumns
Thursday, October 25, 2018 4:53 AM -
Another possibility would be:
RemovedColumnList = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "_ABC")),
RemovedColumns = Table.RemoveColumns(Source, RemovedColumnList)
- Marked as answer by Tamiri7 Thursday, October 25, 2018 3:58 PM
Thursday, October 25, 2018 3:53 PM -
Colin
Upvoted your option as it's much more straightforward. Thanks for sharing
Thursday, October 25, 2018 5:48 PM -
Elegant!
Works smooth
- Edited by Tamiri7 Friday, October 26, 2018 8:15 AM
Friday, October 26, 2018 8:14 AM -
Thank you! Works like a charmFriday, October 26, 2018 8:14 AM