Power Query: select first column for function Table.SelectRows

[Zdenek_Moravec]

Dear coleagues,

I get a table like this

ID	Name	City
1	A	Prague
2	B	Stuttgart
3	C	Cardiff
COUNT:	3

I want to filter out the row COUNT in the first column of the table:

= Table.SelectRows(Source, each ([ID] <> "COUNT:"))

The problem is, that the first column name can be different:

Name	City
A	Prague
B	Stuttgart
C	Cardiff
COUNT:	3

How to replace the hard coded column name in the SelectRows formula?

I tried to get first column as a text or as a table, but it does not work for the "each" construction in the SelectRows formula

let
Source = Excel.CurrentWorkbook(){[Name="Tabulka1"]}[Content],
GetColumnNames = Table.ColumnNames(Source),
GetFirstColumn = List.First(GetColumnNames),
FirstColAsTable = #table(1, {{GetFirstColumn}}),
#"Filtered Rows" = Table.SelectRows(Source, each (FirstColAsTable <> "COUNT:"))
in
#"Filtered Rows"

Thank You

Zdenek Moravec

Cesky Krumlov, Czech Republic

[#2 · Accepted solution]

@Zdenek_Moravec See attached workbook. It contains two tables based on your example where the row with "Count:" in whichever the first column is called in a table, is filtered out.

[#3]

@Zdenek_Moravec 

How about simply this?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FirstColumn = Table.ColumnNames ( Source ){0},    
    FilteredRows = Table.SelectRows(Source, each (Record.Field (_, FirstColumn) <> "Count:"))
in
    FilteredRows

[#4]

Hello @Riny_van_Eekelen,
Record.Field (_, Columnname) was the magic keyword. Then the shrinked step is
= Table.SelectRows(Source, each (Record.Field (_, List.First(Table.ColumnNames(Source))) <> "COUNT:"))
works prefect, thank You!
Zdenek

[#5]

Yet easier, perfect!

[#6]

@Zdenek_Moravec 

As variant

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FilteredRows = Table.SelectRows(Source, each (Record.FieldValues(_){0} <> "Count:"))
in
    FilteredRows