Forum Discussion
Dynamic Filter Based on Column Name
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
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.
4 Replies
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.
The are multiple columns, and I want to reference to the selected column by the column name.
Could you suggest the code?
Thanks.
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.
