Let's say you have table "Table1". Its first column is called "Ref" followed by a number of other columns.

Before grouping by "Ref", find the column names of the first and last 'other' columns (i.e. the 2nd and the last columns of the table).

Expand by using the variable names "first" and "last". Some example code below:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
first = List.First (List.Skip (Table.ColumnNames (Source),1)),
last = List.Last (Table.ColumnNames (Source)),
Group = Table.Group(Source, {"Ref"}, {{"Custom", each _, type table }}),
Expand = Table.ExpandTableColumn(Group, "Custom", {first, last})
in
Expand

If this doesn't make sense, please upload a file that demonstrates your actual situation.

Cross-posted here: https://www.mrexcel.com/board/threads/power-query-to-dynamically-select-first-and-last-column.1259256/

I was able to resolve it myself and used the following formula in a custom column.  It looks at the "table" values in the Custom column if there is a single column it return that but if there are multiple columns it takes the first column and the last column, which is what I want.

However, when there are multiple columns it also takes the header of the first column (which is always Column1) and the last column i.e. Column4. Is there a way to amend the formula below so that it renames the last column to Column2 so that when I click the expand button the "table" values are either aligned under Column1 and Column2 names?

Table.SelectColumns([Custom],if Table.ColumnCount([Custom])=1 then {Table.ColumnNames([Custom]){0}} else {Table.ColumnNames([Custom]){0},Table.ColumnNames([Custom]){Table.ColumnCount([Custom])-1}})

@Velouria Looks like when I posted it was under "Awaiting Moderation" and when I clicked to delete it, it didn't delete the post.  Not my fault. I was actually surprised it still posted. Perhaps you should work with the owner to see why the post doesn't get deleted when you click the delete button.

That's not really the point. All you need to do (here and in other forums) is add links to any cross-posts. Nobody is saying you can't do it.