none
What's Preventing Query Folding in this Power Query? RRS feed

  • Question

  • I've watched the Power Query presentation at the 2014 Tech Ed, and I'm trying to understand why this PQ (below) doesn't employ query folding.  The Tech Ed Power Point presentation states suggests that "Internal Power Query logic" affects folding, but I'm unclear what that means.  Are the Text functions inhibiting query folding?  If so, how might I "scrub" arguments in a custom function parameter list while still encouraging query folding?

    let
        Source = Teradata.Database("fsltdprd.am.freescale.net"),
        
        Product  = Source{[Schema="EDW",Item="PROD_PDM_EFF_CURR"]}[Data],
        Prodline = Source{[Schema="EDW",Item="PROD_LN_PDM_DOC"]}[Data],
     
        ProdFilter = Table.SelectRows(Product, 
            each [MTRL_TYPE_CD] = Text.Upper(Text.Trim("AA")) 
            and  Date.From([REVSN_RLSE_DTTM]) >= #date(2014,1,1)),
    
        PrdlFilter = Table.SelectRows(Prodline, 
            each [TM_VAR_STS_FLG] = "A" 
            and  [EFF_TO_DT] = #date(3999,12,31) 
            and  Text.StartsWith([PTI_CD],Text.Upper(Text.Trim("J")))),
    
        ProdCols = Table.SelectColumns(ProdFilter, 
            {"PART_ID","MTRL_TYPE_CD","PLC_CD","PAO_CD","REVSN_RLSE_DTTM","PROD_LN_ID"}), 
        PrdlCols = Table.SelectColumns(PrdlFilter, {"DOC_ID","PTI_CD","PKG_CD"}),
    
        Join1 = Table.Join(ProdCols, {"PROD_LN_ID"}, PrdlCols, {"DOC_ID"}, JoinKind.Inner), 
    
        Results = Table.RemoveColumns(Join1, {"DOC_ID"})
    
    in
        Results

    Tuesday, November 11, 2014 3:44 PM

Answers

  • I would need to spend a little more time to be sure, but offhand I'd say it's the "Date.From" that prevents folding. Text.Upper, Text.Trim and Text.StartsWith should all be okay. If the underlying field is datetime, you could probably make this succeed by just doing a datetime comparison. If it's text, then I'd also need to do a little research to see if it's possible.
    • Marked as answer by Mark Weisman Tuesday, November 11, 2014 7:44 PM
    Tuesday, November 11, 2014 7:01 PM

All replies

  • I would need to spend a little more time to be sure, but offhand I'd say it's the "Date.From" that prevents folding. Text.Upper, Text.Trim and Text.StartsWith should all be okay. If the underlying field is datetime, you could probably make this succeed by just doing a datetime comparison. If it's text, then I'd also need to do a little research to see if it's possible.
    • Marked as answer by Mark Weisman Tuesday, November 11, 2014 7:44 PM
    Tuesday, November 11, 2014 7:01 PM
  • Okay, the above code runs much faster without Date.From().   

    Curt, I appreciate your guidance.

    P.S., Has anyone considered creating a website with a list of PQ functions that will invariably hinder query folding?  And, of course, it would be helpful if such a website was revised with each new release of Power Query.

    Tuesday, November 11, 2014 7:38 PM
  • Btw, this is the "finished" product ... 

    (optional #"Pti 1 thru 4" as text
        , optional #"Mtrl Type" as text
        , optional #"Last Revision Dttm" as datetime) =>
    
    let
        Source = Teradata.Database("fsltdprd.am.freescale.net"),
    
        MtrlType = Text.Upper(Text.Trim(#"Mtrl Type")), 
        PtiCode  = Text.Upper(Text.Trim(#"Pti 1 thru 4")), 
        LRevDttm = #"Last Revision Dttm",
        
        Product  = Source{[Schema="EDW",Item="PROD_PDM_EFF_CURR"]}[Data],
        Prodline = Source{[Schema="EDW",Item="PROD_LN_PDM_DOC"]}[Data],
     
        ProdFilter = Table.SelectRows(Product, 
            each (if MtrlType = null then true else [MTRL_TYPE_CD] = MtrlType ) 
            and  (if LRevDttm = null then true else [REVSN_RLSE_DTTM] >= LRevDttm )),
    
        PrdlFilter = Table.SelectRows(Prodline, 
            each [TM_VAR_STS_FLG] = "A" 
            and  [EFF_TO_DT] = #date(3999,12,31) 
            and  (if PtiCode = null then true else Text.StartsWith([PTI_CD], PtiCode ))),
    
        ProdCols = Table.SelectColumns(ProdFilter, 
            {"PART_ID","MTRL_TYPE_CD","PLC_CD","PAO_CD","REVSN_RLSE_DTTM","PROD_LN_ID"}), 
        PrdlCols = Table.SelectColumns(PrdlFilter, {"DOC_ID","PTI_CD","PKG_CD"}),
    
        Join1 = Table.Join(ProdCols, {"PROD_LN_ID"}, PrdlCols, {"DOC_ID"}, JoinKind.Inner), 
    
        Results = Table.RemoveColumns(Join1, {"DOC_ID"})
    
    in
        Results


    Tuesday, November 11, 2014 7:57 PM
  • We'd like to do more to let you know which parts of your query can be run on the server and which force local execution, but that's more of a longer-term project. I'll look into whether we think we can provide some of this documentation as a shorter-term approach.

    Did you guess earlier that user-defined functions might inhibit folding and then edit that out? If so, yes, I think that's right. Otherwise, it was just my imagination.

    Tuesday, November 11, 2014 9:49 PM
  • No, you weren't imagining ... I had concluded that query folding was hindered by a user-defined function only to discover that I had a misplaced parenthesis in my code.  Why it got by the syntax checker I'll never know.  But, after I fixed that discrepancy the user-defined function invocation also produced query folding.  My posted "finished" product works like a charm.  It returns results from our mainframe to my desktop in roughly 30 seconds.
    Tuesday, November 11, 2014 9:56 PM
  • I'm glad to hear it!
    Tuesday, November 11, 2014 10:00 PM