Power Query Function: functiionGetNamedRange appears to be not working

[ND_Pard]

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.

[ImkeF]

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]

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]

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]

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]

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]

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

Best of luck!

[ND_Pard · Accepted solution]

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

[Anonymous]

Hi @ND_Pard ,

It seems that the issue is fixed, can you mark your reply as the answer?

More people will benfit.

Best Regards,

Stephen Tao

[ND_Pard]

AlexOlsen, It takes about 70 seconds to update when Hard-Coded; i.e., when the Advanced Editor is as follows: let Source = Oracle.Database("server name", [HierarchicalNavigation=true]), ND_EMARS = Source{[Schema="ND_EMARS"]}[Data], M_CLM_TB1 = ND_EMARS{[Name="M_CLM_TB"]}[Data], #"Filtered Rows1" = Table.SelectRows(M_CLM_TB1, each [PRCS_PER_YM] = "202111" and not Text.StartsWith([FIN_RSN_CD], "3") and [FIN_RSN_CD] <> "248" and [FIN_RSN_CD] <> "249"), Truly, thank you for your responses. Using the functionGetNamedRange has always worked in the past; I have no idea why it is not working now. Sincerely, ND_PARD January 4, 2022 11:08 AM CST

[ImkeF]

Hi @ND_Pard ,
I am not aware of any changes in the folding behaviour of Oracle datasources, but you might want to ask around or log a bug report for it.
Until then, you could write a query that does this filtering on the Oracle side like described here (for MS SQL, but should work for Oracle similarly: Solved: Re: Parameterized SQL Query with query folding - Microsoft Power BI Community )
Just be aware that there will be no folding afterwards - but that seems to be better than to have no folding at all...

[ND_Pard]

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