none
Remove columns based on columns names in Power Query RRS feed

  • 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


    • Edited by Lz._ Thursday, October 25, 2018 5:52 AM Improvement
    • Marked as answer by Tamiri7 Thursday, October 25, 2018 3:58 PM
    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


    • Edited by Lz._ Thursday, October 25, 2018 5:52 AM Improvement
    • Marked as answer by Tamiri7 Thursday, October 25, 2018 3:58 PM
    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 charm
    Friday, October 26, 2018 8:14 AM