Excel 2016 + Power Query + Oracle + Parameters/Variables

[vjunior1981]

Hi everybody,

first of all, sorry for my english!

I have this scenario:

Office 2016, connecting to a Oracle Database 11g, and running this query:

WITH tbl 
     AS (SELECT c.codchamado        AS chamado, 
                c.strtitulo         AS titulo, 
                t.strtipo           AS tipo, 
                c.strnomeusuario    AS solicitante, 
                s.strstatus         AS status, 
                c.datdatacritica    AS data_critica, 
                c.datcreated        AS data_abertura, 
                c.datworkend        AS data_encerramento, 
                cf.strclassificacao AS classificacao, 
                cf.stremail         AS email_classificacao, 
                usu2.strnome        AS resolvedor, 
                Max(t2.codhistory)  max_CODHISTORY 
         FROM   service_desk.tblchamado c 
                join service_desk.tblusuario usu 
                  ON usu.codusuario = c.codusuario 
                left join service_desk.tblusuario usu2 
                       ON usu2.codusuario = c.codresolvedor 
                join service_desk.tblstatus s 
                  ON s.codstatus = c.codstatus 
                join service_desk.tblmodoabertura m 
                  ON m.codmodo = c.codmodoabertura 
                left join service_desk.tblclassificacao cf 
                       ON cf.codclassificacao = c.codclassificacao 
                left join service_desk.tblfornecedor f 
                       ON f.codfornecedor = c.codfornecedor 
                left join service_desk.tbltipo t 
                       ON t.codtipo = c.codtipo 
                left join service_desk.tblportifolio ptf 
                       ON ptf.codportifolio = c.codportifolio 
                left join service_desk.tblchamadohistory t2 
                       ON t2.codchamado = c.codchamado 
         WHERE  c.codclassificacao = 2 
                AND s.bitativo = 1 
         GROUP  BY c.codchamado, 
                   c.strtitulo, 
                   t.strtipo, 
                   c.strnomeusuario, 
                   s.strstatus, 
                   c.datdatacritica, 
                   c.datcreated, 
                   c.datworkend, 
                   cf.strclassificacao, 
                   cf.stremail, 
                   usu2.strnome) 
SELECT t.chamado, 
       t.titulo, 
       t.tipo, 
       t.solicitante, 
       t.status, 
       t.data_critica, 
       t.data_abertura, 
       t.data_encerramento, 
       t.classificacao, 
       t.email_classificacao, 
       t.resolvedor, 
       t1.strtext AS ultima_interacao 
FROM   tbl t 
       left join service_desk.tblchamadohistory t1 
              ON t1.codhistory = t.max_codhistory 
WHERE  t.status = 'Encerrado' 
       AND t.data_encerramento between to_date('01/01/2018', 'dd/mm/yyyy hh24:mi:ss') and  to_date('03/01/2018', 'dd/mm/yyyy hh24:mi:ss')
ORDER  BY data_encerramento

So, this works well, no problems. But, there is a way to have two field in a spreadsheet, passing Date1 and Date2, to get the interval between this 2 parameters? Like here:

AND t.data_encerramento between to_date('01/01/2018', 'dd/mm/yyyy hh24:mi:ss') and  to_date('03/01/2018', 'dd/mm/yyyy hh24:mi:ss')

Until now, my power query is like this:

let
    Fonte = Odbc.Query("dsn=renner", "WITH tbl #(lf)     AS (SELECT c.codchamado        AS chamado, #(lf)                c.strtitulo         AS titulo, #(lf)                t.strtipo           AS tipo, #(lf)                c.strnomeusuario    AS solicitante, #(lf)                s.strstatus         AS status, #(lf)                c.datdatacritica    AS data_critica, #(lf)                c.datcreated        AS data_abertura, #(lf)                c.datworkend        AS data_encerramento, #(lf)                cf.strclassificacao AS classificacao, #(lf)                cf.stremail         AS email_classificacao, #(lf)                usu2.strnome        AS resolvedor, #(lf)                Max(t2.codhistory)  max_CODHISTORY #(lf)         FROM   service_desk.tblchamado c #(lf)                join service_desk.tblusuario usu #(lf)                  ON usu.codusuario = c.codusuario #(lf)                left join service_desk.tblusuario usu2 #(lf)                       ON usu2.codusuario = c.codresolvedor #(lf)                join service_desk.tblstatus s #(lf)                  ON s.codstatus = c.codstatus #(lf)                join service_desk.tblmodoabertura m #(lf)                  ON m.codmodo = c.codmodoabertura #(lf)                left join service_desk.tblclassificacao cf #(lf)                       ON cf.codclassificacao = c.codclassificacao #(lf)                left join service_desk.tblfornecedor f #(lf)                       ON f.codfornecedor = c.codfornecedor #(lf)                left join service_desk.tbltipo t #(lf)                       ON t.codtipo = c.codtipo #(lf)                left join service_desk.tblportifolio ptf #(lf)                       ON ptf.codportifolio = c.codportifolio #(lf)                left join service_desk.tblchamadohistory t2 #(lf)                       ON t2.codchamado = c.codchamado #(lf)         WHERE  c.codclassificacao = 2 #(lf)                AND s.bitativo = 1 #(lf)         GROUP  BY c.codchamado, #(lf)                   c.strtitulo, #(lf)                   t.strtipo, #(lf)                   c.strnomeusuario, #(lf)                   s.strstatus, #(lf)                   c.datdatacritica, #(lf)                   c.datcreated, #(lf)                   c.datworkend, #(lf)                   cf.strclassificacao, #(lf)                   cf.stremail, #(lf)                   usu2.strnome) #(lf)SELECT t.chamado, #(lf)       t.titulo, #(lf)       t.tipo, #(lf)       t.solicitante, #(lf)       t.status, #(lf)       t.data_critica, #(lf)       t.data_abertura, #(lf)       t.data_encerramento, #(lf)       t.classificacao, #(lf)       t.email_classificacao, #(lf)       t.resolvedor, #(lf)       t1.strtext AS ultima_interacao #(lf)FROM   tbl t #(lf)       left join service_desk.tblchamadohistory t1 #(lf)              ON t1.codhistory = t.max_codhistory #(lf)WHERE  t.status = 'Encerrado' #(lf)       AND t.data_encerramento between to_date('01/01/2018', 'dd/mm/yyyy hh24:mi:ss') and  to_date('03/01/2018', 'dd/mm/yyyy hh24:mi:ss')#(lf)ORDER  BY data_encerramento")
in
    Fonte

Best regards!

Vitor Jr.

 

[hgalassi]

I'm not sure if you have solved this yet.
I created a one cell table using the steps described in this article: https://exceleratorbi.com.au/pass-excel-parameter-power-query/

and then referenced it in my Excel Power Query like this:
= Odbc.Query("dsn=DBConnection", "select * from table1 where field1 = '"&Number.ToText(parameter1)&"'") in the advanced editor of the power query

from article: https://community.powerbi.com/t5/Desktop/Query-Parameters-Not-Bound-Oracle-Database/td-p/334384

then I just change the value in the cell to update the parameter and hit refresh all, when needed.