Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Remove multiple columns in Power Query

Hi,

Is there a way to remove multiple columns in Power Query or do I need to remove one each time?

 

Based on the above image, i'm checking if the column exists, and if it does, I want to remove it. What I want to do is to be able to remove multiple columns with e.g. "UnitPrice" and "TaxRate". I have also tried to add it outside of the Table.RemoveColumns function but it returns an error.

And a parameter just retuns one at a time so that doesn't seem efficient.

Do you have any working idea? I could merge the columns I want to remove but that's unnecessary processing, especially when it's 10+ columns.

Thankful for any help if it's possible!

Thank you and Merry Christmas!

Kind regards,

Jari

Need Help
Tips and Tricks
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    You can intersect the list of columns you want to remove with the existing column names like this:

    = Table.RemoveColumns(
    RenamedColumns,
    List.Intersect(
        {
            Table.ColumnNames(RenamedColumns),
            {"UnitPrice", "TaxRate"}
        }
    )
)

7 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    You can intersect the list of columns you want to remove with the existing column names like this:

    = Table.RemoveColumns(
    RenamedColumns,
    List.Intersect(
        {
            Table.ColumnNames(RenamedColumns),
            {"UnitPrice", "TaxRate"}
        }
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi,

      That works great, thanks!
      But that comes with a follow-up question. Is it possible to make it dependent on a completely other table?
      So to have a static table with multiple columns which is removing columns in the main table depending on the inputs in the static table?

      Having a small table such as the following:

       



      /Jari

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        4 years ago

        Sure. Use the column OtherTable[TestColumns] instead of Table.ColumnNames(...) in the code I suggested.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    Could you tell me if your problem has been solved by AlexisOlson 's suggestion? If it is, kindly Accept it as the solution. More people will benefit from it.

     

    Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

     

    Best Regards,
    Eyelyn Qin

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    4 years ago

    NewStep = Table.RemoveColumns(PreviousStepName,{the list of column names which you want to remove},2)