remove all columns in a set starting with "Column"

[Anonymous]

In Power Query I would like to remove all columns that do'nt have a header. So I tried to find out how to remove all columns starting with "Column" in the column name. I would like to do this dynamicly because my source files may a different ammount of collumns without headers. 
Can this be done in Power Query M script?

[Anonymous]

Usually I would transpose the columns.

Then under the 1st column, i will remove the 'null' values.

Then re-transpose the columns again before promoting headers.

[Anonymous]

This is how I solved it.

1. Getting the table column names with Table.ColumnNames.
2. Using List.FindText to find the desired string within those columns (e.g. "Column"), and return that as a list. 
3. Returning a list with the difference between all the columns, and the desired columns using List.Difference.
4. Using Table.RemoveColumns to remove columns not found in our list of desired columns.

Note: this returns all columns that contain the input string (e.g. "Column"), not just those starting with that string.

let
    Source = #"Query Containing Table",
    #"Column Names" = Table.ColumnNames(Source),
    #"Columns Containing String" = List.FindText(#"Column Names", "Column"),
    #"List Difference" = List.Difference(#"Column Names", #"Columns Containing String"),
    #"Remove Columns" = Table.RemoveColumns(Source, #"List Difference")
in
    #"Remove Columns"

[CidcleyBarbosa]

Good solution! Alternatively, this code is simpler:

= Table.RemoveColumns(#"Last step",List.FindText(Table.ColumnNames(#"Last step"), "Column"))

[ValeriaBreve]

This really works great and is easy to implement! Thank you! 🙂

[Greg_Deckler · Accepted solution]

Well, in theory you could use Table.ColumnNames to get a list of column names, do some text parsing with Text functions, essentially loop through each value in the Text list coming from ColumnNames and build up a new set that only includes the word Column in them and then feed in the resulting list into Table.RemoveColumns function.

First and last parts are easy. The tricky part is the middle part. I would use Text.Split to split it out to a list because there are a lot of list functions that you could use to potentially do what you want.

https://msdn.microsoft.com/en-us/library/mt211003.aspx