Power Query use column numbers instead of names for row actions

macfuller

Active Member
Joined
Apr 30, 2014
Messages
319
Office Version
  1. 365
Platform
  1. Windows
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.



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.
Power Query:
    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.:confused:

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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
You could combine List.Range with Table.ColumnNames assuming the columns are all adjacent?
 
Upvote 0
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.SelectRows(Source, each List.Count(List.RemoveNulls(Record.ToList(_)))>1)
in
    tbl

Book1
ABCDEFGHIJKLMN
1NamePlayer 1Player 2Player 3Player 4Player 5NamePlayer 1Player 2Player 3Player 4Player 5
2AxAx
3BxBx
4CxxCxx
5DFxx
6EGx
7FxxJx
8GxKx
9HLxx
10INx
11Jx
12Kx
13Lxx
14M
15Nx
16
Sheet1
 
Upvote 0
Solution
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.

Power Query:
   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] <> ""),
 
Upvote 0
_ 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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top