SOLVED

Power Query: select first column for function Table.SelectRows

%3CLINGO-SUB%20id%3D%22lingo-sub-2630841%22%20slang%3D%22en-US%22%3EPower%20Query%3A%20select%20first%20column%20for%20function%20Table.SelectRows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2630841%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20coleagues%2C%3C%2FP%3E%3CP%3EI%20get%20a%20table%20like%20this%3C%2FP%3E%3CTABLE%20width%3D%22176%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3EID%3C%2FTD%3E%3CTD%3EName%3C%2FTD%3E%3CTD%3ECity%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3EPrague%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3EB%3C%2FTD%3E%3CTD%3EStuttgart%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3ECardiff%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECOUNT%3A%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EI%20want%20to%20filter%20out%20the%20row%20COUNT%20in%20the%20first%20column%20of%20the%20table%3A%3C%2FP%3E%3CP%3E%3D%20Table.SelectRows(Source%2C%20each%20(%5BID%5D%20%26lt%3B%26gt%3B%20%22COUNT%3A%22))%3C%2FP%3E%3CP%3EThe%20problem%20is%2C%20that%20the%20first%20column%20name%20can%20be%20different%3A%3C%2FP%3E%3CTABLE%20width%3D%22145px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2268.8833px%22%3EName%3C%2FTD%3E%3CTD%20width%3D%2276px%22%3ECity%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268.8833px%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2276px%22%3EPrague%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268.8833px%22%3EB%3C%2FTD%3E%3CTD%20width%3D%2276px%22%3EStuttgart%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268.8833px%22%3EC%3C%2FTD%3E%3CTD%20width%3D%2276px%22%3ECardiff%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268.8833px%22%3ECOUNT%3A%3C%2FTD%3E%3CTD%20width%3D%2276px%22%3E3%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EHow%20to%20replace%20the%20hard%20coded%20column%20name%20in%20the%20SelectRows%20formula%3F%3C%2FP%3E%3CP%3EI%20tried%20to%20get%20first%20column%20as%20a%20text%20or%20as%20a%20table%2C%20but%20it%20does%20not%20work%20for%20the%20%22each%22%20construction%20in%20the%20SelectRows%20formula%3C%2FP%3E%3CP%3Elet%3CBR%20%2F%3ESource%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Tabulka1%22%5D%7D%5BContent%5D%2C%3CBR%20%2F%3EGetColumnNames%20%3D%20Table.ColumnNames(Source)%2C%3CBR%20%2F%3EGetFirstColumn%20%3D%20List.First(GetColumnNames)%2C%3CBR%20%2F%3EFirstColAsTable%20%3D%20%23table(1%2C%20%7B%7BGetFirstColumn%7D%7D)%2C%3CBR%20%2F%3E%23%22Filtered%20Rows%22%20%3D%20Table.SelectRows(Source%2C%20each%20(FirstColAsTable%20%26lt%3B%26gt%3B%20%22COUNT%3A%22))%3CBR%20%2F%3Ein%3CBR%20%2F%3E%23%22Filtered%20Rows%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20You%3C%2FP%3E%3CP%3EZdenek%20Moravec%3C%2FP%3E%3CP%3ECesky%20Krumlov%2C%20Czech%20Republic%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2630841%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2631634%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%3A%20select%20first%20column%20for%20function%20Table.SelectRows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2631634%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F295513%22%20target%3D%22_blank%22%3E%40Zdenek_Moravec%3C%2FA%3E%26nbsp%3BSee%20attached%20workbook.%20It%20contains%20two%20tables%20based%20on%20your%20example%20where%20the%20row%20with%20%22Count%3A%22%20in%20whichever%20the%20%3CSTRONG%3Efirst%3C%2FSTRONG%3E%20column%20is%20called%20in%20a%20table%2C%20is%20filtered%20out.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2631676%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%3A%20select%20first%20column%20for%20function%20Table.SelectRows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2631676%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F295513%22%20target%3D%22_blank%22%3E%40Zdenek_Moravec%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20about%20simply%20this%3F%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20FirstColumn%20%3D%20Table.ColumnNames%20(%20Source%20)%7B0%7D%2C%20%20%20%20%0A%20%20%20%20FilteredRows%20%3D%20Table.SelectRows(Source%2C%20each%20(Record.Field%20(_%2C%20FirstColumn)%20%26lt%3B%26gt%3B%20%22Count%3A%22))%0Ain%0A%20%20%20%20FilteredRows%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2631678%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%3A%20select%20first%20column%20for%20function%20Table.SelectRows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2631678%22%20slang%3D%22en-US%22%3EHello%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%2C%3CBR%20%2F%3ERecord.Field%20(_%2C%20Columnname)%20was%20the%20magic%20keyword.%20Then%20the%20shrinked%20step%20is%3CBR%20%2F%3E%3D%20Table.SelectRows(Source%2C%20each%20(Record.Field%20(_%2C%20List.First(Table.ColumnNames(Source)))%20%26lt%3B%26gt%3B%20%22COUNT%3A%22))%3CBR%20%2F%3Eworks%20prefect%2C%20thank%20You!%3CBR%20%2F%3EZdenek%3C%2FLINGO-BODY%3E
Contributor

Dear coleagues,

I get a table like this

IDNameCity
1APrague
2BStuttgart
3CCardiff
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:

NameCity
APrague
BStuttgart
CCardiff
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

5 Replies
best response confirmed by Zdenek_Moravec (Contributor)
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.

@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
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

@Zdenek_Moravec 

As variant

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