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 re...
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"}
        }
    )
)
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
Icon for Super User rankSuper 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

    How would that syntax be written? I can't find out any "OtherTable"-function working there when I write it in the Advanced editor. I want to base it on a table named e.g. "Columns to be Removed" with a list of all the names that are the same as the columns i want removed in the main table. I can't find any similar syntax
    Thanks!
    Kind regards,

    Jari

    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      4 years ago

      OtherTable isn't a function. It's what I used as a table name since I didn't know what the "completely other table" you mentioned was called.

       

      That said, I may have misunderstood which list you wanted to replace. I think this modification may be what you're after:

       

      = Table.RemoveColumns(
    RenamedColumns,
    List.Intersect(
        {
            Table.ColumnNames(RenamedColumns),
            #"Columns to be Removed"[TestColumns]
        }
    )
)