Power Query use column numbers instead of names for row actions

[macfuller]

I've inherited a spreadsheet for a golf pool that I'm trying to generalize. Since the golfer and participant names change, I'd like to reference columns by their index number for certain functions instead of relying on hard-coded names.
Participants select who they're backing. I have generic participant names here for the latest Open golfers instead of "Bob", "Fred", etc.

2022 MCC Masters.xlsx
	C	D	E	F
4	Golfer	Player 1	Player 2	Player 3
5	Aaron Jarvis (a)
6	Aaron Rai
7	Aaron Wise
8	Abraham Ancer
9	Adam Scott	x		x
10	Adri Arnaus
11	Adrian Meronk
12	Aldrich Potgieter (a)		x
13	Alex Wrigley
14	Alexander Björk
15	Anthony Quayle
Player Selection

I use this as a connection. Before bumping against the data from The Open - Golf Leaderboard and Results - ESPN I want to eliminate all the golfers whom nobody picked. If no one picked Aaron Jarvis why keep him in the query? In other words, where the values for every player column in a row are null, delete that row. I do this by creating a new column that merges the values from the player columns, and rows with nothing but null values are removed.

    MergeCols = Table.AddColumn(ChangeColTypes, "AllSelect", 
        each Text.Combine(
            { Text.From([Player 1], "en-US"), 
            Text.From([Player 2], "en-US"), 
            Text.From([Player 3], "en-US"), 
            Text.From([Player 4], "en-US"), 
            Text.From([Player 5], "en-US"), 
            Text.From([Player 6], "en-US"), 
            Text.From([Player 7], "en-US"), 
            Text.From([Player 8], "en-US"), 
            Text.From([Player 9], "en-US"), 
            Text.From([Player 10], "en-US"), 
            Text.From([Player 11], "en-US"), 
            Text.From([Player 12], "en-US"), 
            Text.From([Player 13], "en-US"), 
            Text.From([Player 14], "en-US")
            }, 
            ""), 
            type text),
    
    ReplaceSpaces = Table.ReplaceValue(MergeCols," ",null,Replacer.ReplaceValue,{"AllSelect"}),
    RemoveUnselectedGolfers = Table.SelectRows(ReplaceSpaces, each [AllSelect] <> null and [AllSelect] <> ""),

The M wizard hard-codes the column names. I'd like to be able to replace [Player 1] - or [Bob] for that matter - with something along the lines of Table.Columns {1} so that the participant name doesn't matter. I've tried variations of Table.ColumnNames() but it just gives me the column name.

Yes, I could unpivot the participant names, which I in fact do at a later stage, but I'd like to adapt this technique to non-golf larger data sets. If I can drastically reduce the number of rows before running a join query that would greatly help query speed on much larger data.

 

[RoryA]

You could combine List.Range with Table.ColumnNames assuming the columns are all adjacent?

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.SelectRows(Source, each List.Count(List.RemoveNulls(Record.ToList(_)))>1)
in
    tbl

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Name	Player 1	Player 2	Player 3	Player 4	Player 5		Name	Player 1	Player 2	Player 3	Player 4	Player 5
2	A		x					A		x
3	B		x					B		x
4	C			x		x		C			x		x
5	D							F		x	x
6	E							G					x
7	F		x	x				J			x
8	G					x		K	x
9	H							L			x		x
10	I							N		x
11	J			x
12	K	x
13	L			x		x
14	M
15	N		x
16
Sheet1

 

[macfuller]

Thanks Austin Powers, that's a neat trick.

Would you mind stepping through an explanation? I know the "_" is an each operator, but I'm not clear how the code ignores the Golfer column in deciding the rest of the rows are empty.

I had been able to come up with this syntax, but your code doesn't depend on knowing how many columns there are and it renders the next two lines redundant.

   MergeCols = Table.AddColumn(ChangeColTypes, "AllSelect", 
        each Text.Combine(
            { 
            Record.Field(_, Table.ColumnNames(ChangeColTypes){1}), 
            Record.Field(_, Table.ColumnNames(ChangeColTypes){2}), 
            Record.Field(_, Table.ColumnNames(ChangeColTypes){3}), 
            Record.Field(_, Table.ColumnNames(ChangeColTypes){4}), 
            Record.Field(_, Table.ColumnNames(ChangeColTypes){5}), 
            Record.Field(_, Table.ColumnNames(ChangeColTypes){6}), 
            Record.Field(_, Table.ColumnNames(ChangeColTypes){7}), 
            Record.Field(_, Table.ColumnNames(ChangeColTypes){8}), 
            Record.Field(_, Table.ColumnNames(ChangeColTypes){9}), 
            Record.Field(_, Table.ColumnNames(ChangeColTypes){10}), 
            Record.Field(_, Table.ColumnNames(ChangeColTypes){11}), 
            Record.Field(_, Table.ColumnNames(ChangeColTypes){12}), 
            Record.Field(_, Table.ColumnNames(ChangeColTypes){13}), 
            Record.Field(_, Table.ColumnNames(ChangeColTypes){14})
            }, 
            ""), 
            type text),
            ReplaceSpaces = Table.ReplaceValue(MergeCols," ",null,Replacer.ReplaceValue,{"AllSelect"}),
            RemoveUnselectedGolfers = Table.SelectRows(ReplaceSpaces, each [AllSelect] <> null and [AllSelect] <> ""),

 

[JGordon11]

_ represents each table row as the Table.SelectRows function steps through each row. So _ is a record.

Record.ToList(_) turns the record into a list.

List.RemoveNulls then removes the nulls from that list.

List.Count returns how many entries remain in the list with nulls removed.

If the count is greater than 1 then there is data in the golfer column and at least one other column, and the comparison returns true.

Table.SelectRows only keeps the rows where the overall function evaluates to true.