Forum Discussion

Michelle99's avatar
Michelle99
Brass Contributor
Sep 08, 2020
Solved

ODBC data connection with parameter from drop down

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

 

BI & Data Analysis
excel
  • Michelle99's avatar
    Michelle99
    Sep 10, 2020

    SergeiBaklan 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

     

     

12 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 08, 2020

    Michelle99 

    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"
    • Michelle99's avatar
      Michelle99
      Brass Contributor
      Sep 08, 2020
      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.)
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Sep 08, 2020

        Michelle99 

        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

Resources