Forum Discussion

ND_Pard's avatar
ND_Pard
Helper II
4 years ago
Solved

Power Query Function: functiionGetNamedRange appears to be not working

I have a function, obtained from the internet, that uses data from and Excel named range, allowing me to change the range value and refresh the query using the new/updated parameter entered in the na...
Tutorial Requests
  • ND_Pard's avatar
    ND_Pard
    4 years ago

    Just an FYI:

    I have used the functionGetNamedRanges many times in the past and I used it again today on a new query hitting the same database and table ... AND it worked ... GO FIGURE.

    Here's a portion of the Advanced Editor where it works:

    let
    Source = Oracle.Database("put server name here", [HierarchicalNavigation=true, Query="SELECT ND_EMARS.M_CLM_TB.CMS64_TOS_CD, ND_EMARS.M_CLM_TB.CMS64_FORM_CD, ND_EMARS.M_CLM_TB.TCN_ID, ND_EMARS.M_CLM_TB.CMS64_FFYQ, Sum(ND_EMARS.M_CLM_TB.CMS_RPT_PD_AMT) AS SumOfCMS_RPT_PD_AMT
    FROM ND_EMARS.M_CLM_TB LEFT JOIN ND_EMARS.M_VV_TB ON ND_EMARS.M_CLM_TB.STATE_COS_CD = ND_EMARS.M_VV_TB.R_VV_CD
    WHERE (ND_EMARS.M_CLM_TB.PRCS_PER_YM >= '" & functionGetNamedRange("YYYYMM_Start") &"' And ND_EMARS.M_CLM_TB.PRCS_PER_YM <= '" & functionGetNamedRange("YYYYMM_End") & "' And ...

     

    Thanks you to those that responded.  I'm confident that if I just start fresh on the query, I'll get it to work without any hassle.
    For whatever reason, there are simply times when "IT HAPPENS" ... 🙂

ImkeF's avatar
ImkeF
Community Champion
4 years ago

Hi ND_Pard 
you have to remove the quotes around the reference to your named Range like so:

#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [PRCS_PER_YM] = functionGetNamedRange(rng_YYYYMM)),

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    Do you think it would help to define functionGetNamedRange(rng_YYYYMM) as a variable before filtering so that it doesn't try to evaluate the function for each row?

    YYYYMM = functionGetNamedRange(rng_YYYYMM),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [PRCS_PER_YM] = YYYMM ),

     

    • ND_Pard's avatar
      ND_Pard
      Helper II
      4 years ago
      ImkeF, I removed the quotes but get the same results, the query runs, and runs, and runs …. However, thank you for the response. ND_Pard Tuesday, January 4, 2022 9:45 AM CST
    • ND_Pard's avatar
      ND_Pard
      Helper II
      4 years ago
      AlexisOlson, First off, if I click on "Reply" to your post it takes me to a new page and shows "An Unexpeced Error has occurred. Your request failed. …." Second, I am unfamiliar with using variable in the M language, but did try your suggestion. My Code now starts out as: let Source = Oracle.Database("server name here", [HierarchicalNavigation=true]), ND_EMARS = Source{[Schema="ND_EMARS"]}[Data], M_CLM_TB1 = ND_EMARS{[Name="M_CLM_TB"]}[Data], YYYYMM = functionGetNamedRange(rng_YYYYMM), #"Filtered Rows1" = Table.SelectRows(M_CLM_TB1, each [PRCS_PER_YM] = YYYMM and not Text.StartsWith([FIN_RSN_CD], "3") and [FIN_RSN_CD] <> "248" and [FIN_RSN_CD] <> "249"), As before, the query just runs, and runs, and runs …. Thank you for your suggestion … perhaps I entered it wrong above?
      • AlexisOlson's avatar
        AlexisOlson
        Super User
        4 years ago

        That looks like I was suggesting. I wonder if it still isn't buffering YYYMM to local memory. On the chance that it isn't, let try explicitly buffering using List.Buffer.

        let
    Source = Oracle.Database("server name here", [HierarchicalNavigation=true]),
    ND_EMARS = Source{[Schema="ND_EMARS"]}[Data],
    M_CLM_TB1 = ND_EMARS{[Name="M_CLM_TB"]}[Data],
    YYYYMM = List.Buffer({functionGetNamedRange(rng_YYYYMM)}),
    #"Filtered Rows1" = Table.SelectRows(M_CLM_TB1, each [PRCS_PER_YM] = YYYMM{0}),
    [...]

         

        If this doesn't work, can you verify again that the query runs in a reasonable time if you replace the named range with fixed text "202110"?