ODBC data connection with parameter from drop down

[MichellePace]

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

[#2]

@MichellePace 

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"

[#3]

Thank you very much for such a prompt reply. I am quite new at connecting to data from within excel. When you say “generate query”... quite honestly all I know how to do is click the “Get Data” button and enter my sql in there. Could you please tell me how to generate the query and where to write that code?

(Sorry I’m catching up on years here of excel inactivity.)

[#4]

@MichellePace 

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

[#5]

@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):

[#6]

@MichellePace 

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.

[#7 · Accepted 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

[#8]

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?

[#9]

@MichellePace 

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.

[#10]

@Sergei Baklan @MichellePace 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.

[#11]

@MaxMilanka 

I guess it shall be

Booking = Excel.CurrentWorkbook(){[Name="booking"]}[Content]{0}[Column1]

[#12]

@Sergei Baklan, Thank you so much. Found another way of doing it. Really appreciate your feedback.
https://www.youtube.com/watch?v=OTBBX485Fg8

[#13]

@MaxMilanka , glad to know you solved an issue, thank you for feedback