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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,165
Messages
5,485,145
Members
407,481
Latest member
junniec

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top