Parameter in ODBC PowerQuery

[DanielS83]

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]

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]

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

Br
Daniel

 

[DanielS83]

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]

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]

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