Forum Discussion

karunpoudel's avatar
karunpoudel
Regular Visitor
3 years ago
Solved

Query folding (Table.View) handler function to filter rows/add where clause

I am building a custom connector and need to implement query folding when filter is added. What is the On* handler function name and its parameters for this? Is there any documentation on this and all available handler functions?

These doc doesn't contain everything.
https://learn.microsoft.com/en-us/power-query/samples/trippin/10-tableview1/readme

https://learn.microsoft.com/en-us/power-query/power-query-folding

 

Need Help
  • karunpoudel's avatar
    karunpoudel
    3 years ago

    The handler is

    OnSelectRows = (condition as function) =>
    let
        ast = RowExpression.From(condition),
        ...

    We can convert the condition into abstract syntax tree (AST) using:

    RowExpression.From(function as function) as record

    Example:
    For 

    Table.SelectRows(table, each [Metric1]>=2000 or [Metric1]=null or [Metric1]=[Metric2])

    AST is :

    [ Kind = "Binary", Operator = "Or", 
  Left = [ Kind = "Binary", Operator = "Or", 
           Left = [ Kind = "Binary", Operator = "GreaterThanOrEquals",
                    Left = [ Kind = "FieldAccess", MemberName = "Metric1", Expression = RowExpression.Row  ] , 
                    Right = [ Kind = "Constant", Value = 2000 ]
                   ] , 
           Right = [ Kind = "Binary", Operator = "Equals", 
                     Left = [ Kind = "FieldAccess", MemberName = "Metric1", Expression = RowExpression.Row  ] , 
                     Right = [ Kind = "Constant", Value = null ]  
                    ]  
          ] , 
  Right = [ Kind = "Binary", Operator = "Equals", 
            Left = [ Kind = "FieldAccess", MemberName = "Metric1", Expression = RowExpression.Row  ] , 
            Right = [ Kind = "FieldAccess", MemberName = "Metric2", Expression = RowExpression.Row  ]  
           ]  
 ]​

2 Replies

Replies have been turned off for this discussion
  • karunpoudel's avatar
    karunpoudel
    Regular Visitor
    3 years ago

    The handler is

    OnSelectRows = (condition as function) =>
    let
        ast = RowExpression.From(condition),
        ...

    We can convert the condition into abstract syntax tree (AST) using:

    RowExpression.From(function as function) as record

    Example:
    For 

    Table.SelectRows(table, each [Metric1]>=2000 or [Metric1]=null or [Metric1]=[Metric2])

    AST is :

    [ Kind = "Binary", Operator = "Or", 
  Left = [ Kind = "Binary", Operator = "Or", 
           Left = [ Kind = "Binary", Operator = "GreaterThanOrEquals",
                    Left = [ Kind = "FieldAccess", MemberName = "Metric1", Expression = RowExpression.Row  ] , 
                    Right = [ Kind = "Constant", Value = 2000 ]
                   ] , 
           Right = [ Kind = "Binary", Operator = "Equals", 
                     Left = [ Kind = "FieldAccess", MemberName = "Metric1", Expression = RowExpression.Row  ] , 
                     Right = [ Kind = "Constant", Value = null ]  
                    ]  
          ] , 
  Right = [ Kind = "Binary", Operator = "Equals", 
            Left = [ Kind = "FieldAccess", MemberName = "Metric1", Expression = RowExpression.Row  ] , 
            Right = [ Kind = "FieldAccess", MemberName = "Metric2", Expression = RowExpression.Row  ]  
           ]  
 ]​