Forum Discussion
Power Query: select first column for function Table.SelectRows
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
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.
5 Replies
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- Yet easier, perfect!
As variant
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], FilteredRows = Table.SelectRows(Source, each (Record.FieldValues(_){0} <> "Count:")) in FilteredRows
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.
- 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
