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" ... 🙂

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"?

  • ND_Pard's avatar
    ND_Pard
    Helper II
    4 years ago
    AlexOlsen, The Advanced Editor now starts as follows: let Source = Oracle.Database("my 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} and not Text.StartsWith([FIN_RSN_CD], "3") and [FIN_RSN_CD] <> "248" and [FIN_RSN_CD] <> "249"), Unfortuanately, it continues to run, and run, and run …. Thanks again; your comments are appreciated very much. ND_Pard … Tuesday, January 4, 2022 10:57 AM CST
    • AlexisOlson's avatar
      AlexisOlson
      Super User
      4 years ago

      OK. I'm out of ideas if this runs much slower than a hardcoded string.

       

      Best of luck!

      • ND_Pard's avatar
        ND_Pard
        Helper II
        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" ... 🙂