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 ...
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

     

     

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

  • Michelle99's avatar
    Michelle99
    Brass Contributor
    Sep 09, 2020

    SergeiBaklan , 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):

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

      Michelle99 

      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.

      • Michelle99's avatar
        Michelle99
        Brass Contributor
        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

         

         

Resources