Power Query: Refer to a Column by Name Stored as a Text Parameter (REDUX)

Question

I am using Power Query in Excel for Microsoft 365.

The question I am about to pose is very similar to one I recently posed and for which an elegant answer was given. That question is here: Power Query: Refer to a Column by Name Stored as a Text Parameter

In the question linked above, I needed to filter rows based on the nullness of a column whose position was constant (always the first column) but whose name was difficult to use because it was stored in a text parameter. The answer was to demote the headers (thereby giving them predictable names), filter based on the nullness of Column1, and promote the headers (to bring back their original names). The text parameter holding the column's name ended up not coming into play.

In the current question, I again need to filter rows based on the nullness of a column whose name is stored in a text parameter, but this time, it won't always be the first column. It could be the first, second, third, or fourth column. So this time, I really do have to use the parameter that specifies the column name.

I will present my original question again, altered to reflect this new requirement.

Here is my source data table (whose table name is Source):

Col1	Col2	Col3	Col4	Name
11	21	31	41	Dog
12	22	32	42	Cat
	23	33	43	Ostrich
14	24		44	Parakeet
15	25	35	45	Trout

I have defined one text parameter:

• FilterColumn specifies the name of the column that will be used for filtering null values. It can take any of the values Col1, Col2, Col3, or Col4.

My goal is to develop a Power Query to filter out rows where there is a null in the column specified by FilterColumn.

Assuming I set FilterColumn to Col3, the desired result is:

Col1	Col2	Col3	Col4	Name
11	21	31	41	Dog
12	22	32	42	Cat
	23	33	43	Ostrich
15	25	35	45	Trout

Here is the M Language code for my Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Source"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", Int64.Type}, {"Col2", Int64.Type}, {"Col3", Int64.Type}, {"Col4", Int64.Type}, {"Name", Text.Type}}),
    // #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [FilterColumn] <> null and [FilterColumn] <> "")
    // #"Filtered Rows" = Table.SelectRows(#"Changed Type", each FilterColumn <> null and FilterColumn <> "")
in
    #"Filtered Rows"

It is the #"Filtered Rows" applied step that is going wrong. In the code above, I show two (commented-out) attempts I've made at implementing this applied step.

In the first attempt, I simply refer to the column name directly (with brackets). However, FilterColumn is taken to be the name of the column of interest, not as a text parameter holds the column name. An error is thrown because there is no column named FilterColumn: Expression.Error: The field 'FilterColumn' of the record wasn't found.

In the second attempt, I again refer to the column name directly, but this time without brackets. This does not throw an error, but no rows get filtered out. The output is unchanged from the input.

How may I filter out rows based on null values in a column named by the parameter FilterColumn?

Answer 1

You need to pass the parameter as a text string, so try:

#"Filtered Rows" = Table.SelectRows(#"Changed Type", (r)=>Record.Field(r,FilterColumn)<>null)

or

#"Filtered Rows" = Table.SelectRows(#"Changed Type", 
        each Record.Field(_,FilterColumn) <> null 
        and Record.Field(_,FilterColumn) <> "")

Answer 2

You may use this also,,,

#"Filtered Rows" = Table.SelectColumns(Table.SelectRows(#"Changed Type", each [FilterColumn] <> null and [FilterColumn] <> ""), {"Column1", "Column2", ...})

N.B.

In this approach.

1. Table.SelectRows(...) used to filter rows based on the specified condition, & Table.SelectColumns(...) used to select the desired columns from the filtered rows.
2. Replace Column1, Column2, with the actual column names.

This approach combines filtering Row & Column selection in a single step, which can be more efficient and it may make the code more concise.