Power Query dynamic reference to a column

[cr731]

I'm trying to filter a table but use a dynamic reference to determine column is filtered, like this,

Table.SelectRows(MyTable, each ("[" & FieldToFilter & "]" = "FilterValue"))

This isn't working... what am I missing? I'm assuming something is amiss in the "[" & FieldToFilter & "]" section but I can't figure out the correct syntax. I'm trying to replicate [FieldToFilter] but M doesn't seem to work that way.

Thanks

 

[philiplaucpa]

You should use slicer. At the field level put a dummy formula to tell Excel that you are making calculation. Then use the newly create onto your pivot table.

 

[Ozeroth]

You can use Record.Field() to allow a dynamic column name:

Table.SelectRows(MyTable, each (Record.Field(_, FieldToFilter) = "FilterValue"))

 

[cr731]

You should use slicer. At the field level put a dummy formula to tell Excel that you are making calculation. Then use the newly create onto your pivot table.

I'm trying to filter my source data before it gets to PowerPivot though - my source has a few hundred thousand rows and I only need a small selection - so I'm trying to get it filtered down. However, that filter might occur based upon different fields, so I need to have a dynamic way to filter the table.

 

[cr731]

You can use Record.Field() to allow a dynamic column name:

Table.SelectRows(MyTable, each (Record.Field(_, FieldToFilter) = "FilterValue"))

Thanks... that works perfectly. What does the underscore represent?

 

[Ozeroth]

That's good

In this case, the underscore represents a record corresponding to each row of MyTable, which Table.SelectRows iterates over.

In general
each < expression involving _ >
is shorthand for a function taking a single argument represented by the underscore.

So

each (Record.Field(_, FieldToFilter) = "FilterValue")

could be rewritten as

 (CurrentRow) =>  (Record.Field(CurrentRow, FieldToFilter) = "FilterValue")

The Table.SelectRows function expects (as its 2nd argument) a function which takes a single argument of type record and returns a value of type logical. The record argument (represented by the underscore) iterates over each row of the table.

 

[oguruma]

I'm trying to filter a table but use a dynamic reference to determine column is filtered, like this,

Table.SelectRows(MyTable, each ("[" & FieldToFilter & "]" = "FilterValue"))

This isn't working... what am I missing? I'm assuming something is amiss in the "[" & FieldToFilter & "]" section but I can't figure out the correct syntax. I'm trying to replicate [FieldToFilter] but M doesn't seem to work that way.

Thanks

e="each " & pemail & " <> " & """""",

// either
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", Expression.Evaluate("each " & pemail & " <> " & """"""))
// or
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", Expression.Evaluate(e))