09-08-2020 07:21 AM
Hi there,
Via ODBC I am connecting to a Pervasive (version 13) database. Is there a way for me to somehow make use of a parameter? For example, in the below screenshot I've shown (although it is not syntactically correct) that I'd like to somehow use the named cell selected_pperiod as a parameter in my query. Is this even possible? And if it is, how would I go about passing a parameter in my SQL query?
Many thanks,
Michelle
09-08-2020 07:39 AM
Generate query with any hardcoded parameter, e.g. 102. After that in advanced editor add getting parameter string and correct generated query with it, like
let
parameter = Excel.CurrentWorkbook(){[Name="selected_pperiod"]}[Content]{0}[Column1],
...."start query" & parameter & "end query"
09-08-2020 08:00 AM
09-08-2020 08:48 AM
First you initiate connection as (in your case from ODBC)
On next step click Transform (not Load)
Open Home-Advanced Editor for Power Query, it'll be something like
let
Source = Sql.Database("server", "database", [Query="select * from Department where Id=8"])
in
Source
Modify code as
let
parameter = Excel.CurrentWorkbook(){[Name="selected_pperiod"]}[Content]{0}[Column1],
Source = Sql.Database("server", "database",
[Query="select * from Department where Id=" & parameter ])
in
Source
Click Done. If works you may load the query from here
09-09-2020 09:36 AM - edited 09-09-2020 10:01 AM
@Sergei Baklan , thank you once again for your reply. I've made some progress by following your suggestion. However, now I have the below error. Sorry to ask for your help for a third time, could you please help me resolve it?
And for what it's worth, I played with the query by converting the parameter to Text. The error changed (a little):
09-09-2020 10:14 AM
Not clear from the screenshot, it could be if [Column1] is missed on first step. What do you see as result if you stay on first step, is it 108?
In any case please add one more step in the middle, I forgot you parameter in Excel is number, we need to convert it to text
parameter = Text.From(PPeriod_parameter),
and use the latest in query string.
09-09-2020 08:39 PM - edited 09-09-2020 08:43 PM
Solution@Sergei Baklan thank you that is exactly it! I added the line you mentioned and it works! For anyone else looking for this solution, here it is:
let
pperiod = Excel.CurrentWorkbook(){[Name="reneirs_pperiod"]}[Content]{0}[Column1],
pperiod_text = Text.From(pperiod),
Source = Odbc.Query("dsn=PASTEL.odbc", "select * from LedgerTransactions where PPeriod = " & pperiod_text)
in
Source
09-09-2020 09:12 PM
I have one Laaaaast Question @Sergei Baklan ... Every time I select a new PPeriod in my yellow dropdown, I have to go and refresh my query for the new data to be reflected in the table. Question: Am I correct that I'll need to write some VBA code to "force" automatic query refresh every time my user selects a new PPeriod?
09-10-2020 07:45 AM
Great to know it finally works, thanks for the result sharing.
Yes, if automatic upon parameter is changed that's VBA. Can't advise with concrete code, but you may google for the pattern or ask as separate question this forum people.