Remove columns based on columns names in Power Query

All replies

• 

  

  1

  Sign in to vote

  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
• 

  

  5

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  Colin

  Upvoted your option as it's much more straightforward. Thanks for sharing

  Thursday, October 25, 2018 5:48 PM
• 

  

  0

  Sign in to vote

  Elegant!

  Works smooth 

  • Edited by Tamiri7 Friday, October 26, 2018 8:15 AM

  Friday, October 26, 2018 8:14 AM
• 

  

  0

  Sign in to vote

  Thank you! Works like a charm

  Friday, October 26, 2018 8:14 AM