# Parameter in ODBC PowerQuery



## DanielS83 (Jun 20, 2019)

Hi Everyone,


I've tried to use a parameter from Excel cell to modify an ODBC Query from AS400 Database like this:


let
BUPKTO = Kunde,
Quelle = Odbc.Query("dsn=AS400", "SELECT BBU00.BUFNR, BBU00.BUBHKZ, BBU00.BUPKTO, #(lf)FROM WWC400.WEXFILE.BBU00 BBU00#(lf)WHERE (BBU00.BUFNR='01') AND (BBU00.BUBHKZ='D') AND (BBU00.BUPKTO='Kunde')")
in
Quelle


"BUPKTO" is defined as a parameter value like this:


let
Quelle = Excel.CurrentWorkbook(){[Name="Tabelle3"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"BUPKTO", type text}}),
BUPKTO = #"Geänderter Typ"{0}[BUPKTO]
in
BUPKTO


Is this working like this, or am I completely wrong...!?


Thx
Daniel


----------



## theBardd (Jun 20, 2019)

You could pull in all of the data from the data from the ODBC query, omit the WHERE clause. Filter he columns for some value, just to generate the M code. Then set variables to the values in the Excel workbook, as you show. Finally, modify the M code to replace the filter values with those variables.


```
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    _value.BUFNR = Source{0}[BUFNR],
    _value.BUBHKZ = Source{0}[BUBHKZ],
    _value.BUPKTO = Source{0}[BUPKTO],
    Quelle = Odbc.Query("dsn=AS400", "SELECT BBU00.BUFNR, BBU00.BUBHKZ, BBU00.BUPKTO, #(lf)FROM WWC400.WEXFILE.BBU00 BBU00#(lf))"),
    BUFNR.Filter = Table.SelectRows(Quelle, each ([BUFNR] = _value.BUFNR)),
    BUBHKZ.Filter = Table.SelectRows(BUFNR.Filter, each ([BUBHKZ] = _value.BUBHKZ)),
    BUPKTO.Filter = Table.SelectRows(BUBHKZ.Filter, each ([BUPKTO] = _value.BUPKTO))
in
    BUPKTO.Filter
```


----------



## DanielS83 (Jun 20, 2019)

Thx...but is this solution much more slowly than making it with an sql statement?

Br
Daniel


----------



## DanielS83 (Jun 20, 2019)

Something is also not working with the code...just tried this out, but getting error message:

  DataSource.Error: ODBC: ERROR [42000] [IBM][System i Access ODBC-Treiber][DB2 für i5/OS]SQL0104 - Token . ungültig. Gültige Token: , FROM INTO.Details:
    DataSourceKind=Odbc
    DataSourcePath=dsn=WWC400
    OdbcErrors=Table

Do you have an idea about this?

Thx
Daniel


----------



## theBardd (Jun 20, 2019)

The query might be slower, I guess it depends upon how much data you have. I am not sure if query folding would apply, my guess is that with an ODBC query it does not, so maybe doing it in the query would be faster



```
let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle3"]}[Content],
    _value.BUFNR  = Source{0}[BUFNR],
    _value.BUBHKZ = Source{0}[BUBHKZ],
    _value.BUPKTO = Source{0}[BUPKTO],
   _sql.Select   = "SELECT BBU00.BUFNR, BBU00.BUBHKZ, BBU00.BUPKTO, #(lf)",
   _sql.From     = "FROM WWC400.WEXFILE.BBU00 BBU00#(lf)",
   _sql.Where    = "WHERE (BBU00.BUFNR='" & _value.BUFNR & "') AND (BBU00.BUBHKZ='" & _value.BUBHKZ & "') AND (BBU00.BUPKTO='" & _value.BUPKTO & "')",
    Quelle = Odbc.Query("dsn=AS400", _sql.Select & _sql.From & _sql.Where)
in
    Quelle
```
 
No idea as to what went wrong I am afraid, it is a language unknown to me, and I don't have access to the database.


----------



## DanielS83 (Jun 20, 2019)

The solution is so much more easier:


```
let
    Quelle = Odbc.Query("dsn=WWC400", "select * from bbu00 where BUFNR = '"&BUFNR&"'and BUBHKZ = '"&BUBHKZ&"' and BUPKTO ='"&BUPKTO&"'")
in
    Quelle
```


----------

