Sep 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
Sep 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"
Sep 08 2020 08:00 AM
Sep 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
Sep 09 2020 09:36 AM - edited Sep 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):
Sep 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.
Sep 09 2020 08:39 PM - edited Sep 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
Sep 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?
Sep 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.
Dec 03 2021 06:06 AM
@Sergei Baklan @Michelle99 This has been very useful so far. H'ever, I'm having an error like in the screenshot and will you able to help? Unable to convert to text as well. Appreciate your help.
Dec 03 2021 08:41 AM
I guess it shall be
Booking = Excel.CurrentWorkbook(){[Name="booking"]}[Content]{0}[Column1]
Dec 03 2021 01:12 PM
@Sergei Baklan, Thank you so much. Found another way of doing it. Really appreciate your feedback.
https://www.youtube.com/watch?v=OTBBX485Fg8
Dec 04 2021 06:47 AM
@MaxMilanka , glad to know you solved an issue, thank you for feedback
Sep 09 2020 08:39 PM - edited Sep 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