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

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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