Parameter in ODBC PowerQuery

DanielS83

New Member
Joined
Jun 20, 2019
Messages
4
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
Joined
Jan 21, 2012
Messages
912
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
Joined
Jun 20, 2019
Messages
4
Thx...but is this solution much more slowly than making it with an sql statement?

Br
Daniel
 

DanielS83

New Member
Joined
Jun 20, 2019
Messages
4
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
Joined
Jan 21, 2012
Messages
912
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
Joined
Jun 20, 2019
Messages
4
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
 

Forum statistics

Threads
1,078,437
Messages
5,340,270
Members
399,361
Latest member
Linford

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top