Results 1 to 6 of 6

Thread: Parameter in ODBC PowerQuery
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Parameter in ODBC PowerQuery

    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

  2. #2
    Board Regular
    Join Date
    Jan 2012
    Posts
    844
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Parameter in ODBC PowerQuery

    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

  3. #3
    New Member
    Join Date
    Jun 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Parameter in ODBC PowerQuery

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

    Br
    Daniel

  4. #4
    New Member
    Join Date
    Jun 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Parameter in ODBC PowerQuery

    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

  5. #5
    Board Regular
    Join Date
    Jan 2012
    Posts
    844
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Parameter in ODBC PowerQuery

    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 by theBardd; Jun 20th, 2019 at 08:41 AM.

  6. #6
    New Member
    Join Date
    Jun 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Parameter in ODBC PowerQuery

    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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •