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 named range.  The function is named: GetNamedRange and is written as follows:

 

let GetNamedRange=(NamedRange) =>

let
    name = Excel.CurrentWorkbook(){[Name=NamedRange]}[Content],
    value = name{0}[Column1]
in
    value

in GetNamedRange

 

One of the lines in my Advanced Editor works and is written:

 

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

 

I want to replace the "202110" value in the above line with the data entered into my range named: rng_YYYYMM.

I modified the line above as follows:

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

 

Currently, the range contains the same text value that was in the code, i.e., the text value of the range: rng_YYYYMM is 202110
Prior to changing, the query would refresh in about 1 miinute.  Now, unfortunately, it reads through the entire database of over a billion records and ... well, it never finishes .... it just runs, and runs, and runs.
I've used this function many times in the past and it work GREAT at allowing me to simply change the range value and "Refresh" the query.  Why is it not working now ... HELP.

 

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

11 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion

    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

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