Join Queries that collect variables from Excel and will be used in a sql to gather data from an Oracle Database in one query.

Question

Hello,

I made several queries that convert an excel value into a variable that can be used in a power excel query that uses a sql-program to collect data from an oracle database.

After changing the value in Excel i need to refresh all the queries separately and that i want to change.
So I want to make one query of the individual queries (that convert the excel values into parameters) that i have at this moment.

In attachment you can find a printscreen of the queries that convert the value in to a parameter and the sheet where you can select the value which will be converted into a parameter.
Also in attachment the syntax of each query which i want to join in one query. The syntax of these queries you can also found below here.

Can someone tell me i this is possible because each individual query results in a table in excel with the wanted parameter value and if it is possible can someone make one query of these several queries so that i know how i can do this for the future.

Current
let
Source = Excel.CurrentWorkbook(){[Name="Cur"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CurPart", type text}}),
CurName = #"Changed Type"{0}[CurPart]
in
CurName

Previous
let
Source = Excel.CurrentWorkbook(){[Name="Prev"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PrevPart", type text}}),
PrevName = #"Changed Type"{0}[PrevPart]
in
PrevName

SubCon1
let
Source = Excel.CurrentWorkbook(){[Name="Suba"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SubConTit1", type text}}),
SubNameA = #"Changed Type"{0}[SubConTit1]
in
SubNameA

SubCon2
let
Source = Excel.CurrentWorkbook(){[Name="Subb"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SubConTit2", type text}}),
SubNameB = #"Changed Type"{0}[SubConTit2]
in
SubNameB

Thank You In Advance

My Best Regards

94898-content-of-the-separate-queries.txt

Answer 1

Hi @Marc Bergmans

All you need is in Power Query / Get & Transform - Pass value as query parameter. Basically, put all your paramaters/variable in 1 table, create a connection on that table => 1 query only
Any question let me know

Answer 2

Is this also possible if this parameters have to be used in an sql-query.
The queries i added the previous time delivers the parameters ('" & Previous & "','" & Current & "') and ('" & SubCon1 & "','" & SubCon2 & "', '" & SubCon3 &"', '" & SubCon4 &"', '" & SubCon5 &"')
They will be used in a way like shown below.

let
Source = Oracle.Database("r2.pro.grk.nl", [Query="select pk,
substr(cref,8,4),
c_d,
B_C,
C_C,
A_1,
int,
cy_c,
m_d_s,
g_ften,
round(sum(i_o),2) as i_o,
round(sum(n_o),2) as n_o,
round(l_w_o,2) as l_w_o,
case when g_ften like ('G_1.2.%') then round(sum(-1 * (nvl(N_O, 0)+nvl(I_O, 0))),2) else round(sum(-1*nvl(L_A_O,0)),2) end as A_O
from r2.t_lr
where pk in ('" & Previous & "','" & Current & "') and substr(cref,8,4) in ('" & SubCon1 & "','" & SubCon2 & "', '" & SubCon3 &"', '" & SubCon4 &"', '" & SubCon5 &"')
group by pk, substr(cref,8,4), c_d, B_C, C_C, A_1, int, cy_c, m_d_s, g_ften, l_w_o"])
in
Source

Thanks in Advance

Answer 3

@Marc Bergmans

No problem. This is explained in Power Query / Get & Transform – SQL native query with parameter
I'm not sure for Oracle but guess you could even use the Value.NativeQuery approach