Forum Discussion
Power Query Function: functiionGetNamedRange appears to be not working
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" ... 🙂
OK. I'm out of ideas if this runs much slower than a hardcoded string.
Best of luck!
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" ... 🙂
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
- 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
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...
