Dynamic headers

[Anonymous]

Morning,

I have a merge query and want to expand all the columns bar one, but some of the column headers are likely to change so it needs to be somewhat dynamic. 

I saw one way to expand all columns dynamically is to input two lines like this:

step2 = Table.AddColumn(#"step1", "Custom", each Table.ColumnNames ( Table.Combine ( #"step 1"[Transform File] ) )),

step 3= Table.ExpandTableColumn ( #"step 1", "PN Rank", step2 ),

But I want step2 to create a list minus one column. Is this possible? for example if it was to create the column names table but i wanted it to remove the column "part number" is there a way to do something like:

step2 = Table.AddColumn(#"step1", "Custom", each Table.ColumnNames ( Table.Combine ( #"step 1"[Transform File] Except [part number] ) )),

Thanks.

[AlexisOlson]

Assume the table you merged in is called Table2. Then you can define the columns you want from this table and then use that list in your expansion step.

ColsToExpand = List.Select(Table.ColumnNames(Table2), each _ <> "UnwantedColumnName" ),
#"Expanded Column" = Table.ExpandTableColumn(#"Merged Queries", "Table2", ColsToExpand)

You'll need to adjust the table names and references as appropriate for your situation.

[Daryl-Lynch-Bzy]

Hi @Anonymous - try breaking this down into more steps so you can apply a filter to the list of Column Names to remove the unwanted column.  It you look something like this:

Source = ABC with Nested Table,
ColumnNames = Table.ColumnNames(Source, Nested Table),
Filter = Table.SelectRows(ColumnNames, "Column1", each filter condition),
list = Filter[Column1],
Expand = Table.ExpandColumns( Source, "Nested Table", list )