none
PowerQuery - Remove columns with specific character in header RRS feed

  • Question

  • Hello!

    I don't know if this is possible... I have a large table with a lot of "prep" columns.

    Let's say I have this table 

    [Unit]

    [Ratio1_prep1]

    [Ratio1]

    [Var_Ratio1]

    [Ratio2_prep1]

    [Ratio2_prep2]

    [Ratio2_prep3]

    [Ratio2]

    [Var_Ratio2]

    and I only want to keep these columns 

    [Unit]

    [Ratio1]

    [Ratio2]

    So what I need is to remove every column where the header starts with 'Var' or where it ends with 'prep%'.

    I know I can select the columns I need and use "Remove other columns" but there is a risk that I forget some by mistake when there is a lot of them or when a new ratio is added by someone else.

    Thank you!

    Friday, December 4, 2015 4:33 PM

Answers

  • Hi PLRD. Try adding this custom step to your query:

    = let columnsToRemove = List.Select(Table.ColumnNames(PreviousStepName), each Text.StartsWith(_, "Var") or Text.Contains(_, "_prep")) in Table.RemoveColumns(PreviousStepName, columnsToRemove)

    Note that it does a "contains" when looking for "_prep", so you'll want to ensure it's not removing columns you actually want to keep.

    Ehren


    Friday, December 4, 2015 5:40 PM
    Owner

All replies

  • Hi PLRD. Try adding this custom step to your query:

    = let columnsToRemove = List.Select(Table.ColumnNames(PreviousStepName), each Text.StartsWith(_, "Var") or Text.Contains(_, "_prep")) in Table.RemoveColumns(PreviousStepName, columnsToRemove)

    Note that it does a "contains" when looking for "_prep", so you'll want to ensure it's not removing columns you actually want to keep.

    Ehren


    Friday, December 4, 2015 5:40 PM
    Owner
  • Thanks a lot Ehren!
    Friday, December 4, 2015 6:03 PM