Dynamic Filter Based on Column Name

[Mic1979]

Hello,

I have the following table:

and I would like to apply a filter based on the column name, e.g. Distribution.

I was trying to write the following code:

SelectDistributionColumn = List.Select (Table.ColumnNames (RemovedColumn), each Text.Contains (_, "Distribution")),

FilteredTable = Table.SelectRows (Previous_Step, each Record.Field(_, SelectDistributionColumn) <>0)

but this is not working.

Could you help?

Thanks

[ronrsnfld]

SelectDistributionColumn is a List, but needs to be a text string, as indicated by the error message you most likely received.

If there is just a single column, then just use the first element of that list.

If there are multiple columns, then you have to reference each column separately.

[Mic1979]

The are multiple columns, and I want to reference to the selected column by the column name.

Could you suggest the code?

Thanks.

[ronrsnfld · Accepted solution]

One method is to add a "Filter Column" which will return True or false depending on your logic. Then filter on that column.

For example, the following code would add a column name'd "Filter"

#"Add Filter Column" = Table.AddColumn(PreviousStep,"Filter", (r)=> 

    //Use List.AllTrue if you want "and" logic
    //Use List.AnyTrue if you want "or" logic
        List.AllTrue(
            
            List.Transform(
                Record.FieldValues(
                    Record.SelectFields(
                        r,SelectDistributionColumn)), 
            each _ <> 0 and _ <> null)))

Then Filter on that column for true or false.

[Mic1979]

Thanks.