Remove multiple columns in Power Query

[Anonymous]

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

[wdx223_Daniel]

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

[Anonymous]

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

[AlexisOlson · Accepted solution]

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]

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]

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

[Anonymous]

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]

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]
        }
    )
)