Parameter in ODBC PowerQuery

DanielS83

New Member
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

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

Code:
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

New Member
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

Rules violation
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


Code:
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.
 
Last edited:

DanielS83

New Member
The solution is so much more easier:

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

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top