Search code examples
excelpowerquerym

Pass list to Table.RemoveColumns

I am looking for a way to pass a list to the Table.RemoveColumns() step in Power Query.

Overview of the set up, two tables as data sources, one is a config table with all the column names of the second data source with simple 'yes' 'no' selectors identifying which columns should be kept/removed. This table is used as a data source, filtered by 'no', and drilled down as a list like so: enter image description here

I am looking for a way to pass that list to a step to remove columns in my 'data' source:

So the step to remove columns:

= Table.RemoveColumns(Source,{"InvoiceDate", "T/S Start Date", "TotalBreakMinutes"})

Would become:

= Table.RemoveColumns(Source,{cols})

However you can't pass a list to an argument that expects text. I tried a few work arounds like adding a prefix " and suffix " to each list item and using Text.Combine with a comma separator however Table.RemoveColumns step handles the string as a single column

enter image description here

Is there a way to pass that list as a recognisable condition for Table.RemoveColumns()?

Solution

  • = Table.RemoveColumns(Source,cols) where cols is a list of column names

    sample code

    let Source = #table({"Column1", "Column2","Column3","Column4"},{{"A","B","C","D"}}),
removetable = #table({"Column1"},{{"Column1"},{"Column2"}}),
removelist = removetable[Column1],
#"Removed Columns" = Table.RemoveColumns(Source,removelist)
in #"Removed Columns"