none
Question about Power Query Variable passing RRS feed

  • Question

  • I am new at using Power Query. I have a working SQL query and am trying to make it dynamic by pulling information from a cell in the spreadsheet. That information is held in a Table.

    In the query, I can see that my coding is pulling in the right period (2), but I can not get the SQL to run by inserting the variable (vPeriod) for the number 2.

    If I can get this to work, it will be a big win at work.

    Here is my code from Power Pivot...

    let
     Parameter = Excel.CurrentWorkbook(){[Name="TParam"]}[Content],
     vPeriod=Parameter{0}[Value],

     JEStatus = Oracle.Database("EFPROD01", [Query="SELECT A.BUSINESS_UNIT as BU, A.JOURNAL_ID as JE, a.descr as Name, TO_CHAR(A.JOURNAL_DATE,'YYYY-MM-DD') as JEDate, a.attachment_exist as Att, A.SOURCE as Src, A.JRNL_HDR_STATUS as Status, A.JRNL_PROCESS_REQST as Rqst, A.OPRID, b.oprdefndesc as Employee, c.supervisor_id as Approver, A.DTTM_STAMP_SEC

        FROM SYSADM.PS_JRNL_HEADER A, sysadm.PSOPRDEFN b, sysadm.ps_CBI_GL_APPROVAL c

        WHERE a.oprid = b.oprid
        and a.oprid = c.oprid
        and A.JRNL_PROCESS_REQST IN ('G','N','E')
        AND A.JRNL_HDR_STATUS NOT IN ('D','P','U')
        AND A.LEDGER_GROUP = 'ACTUALS'
        and A.ACCOUNTING_PERIOD = 2
        and a.fiscal_year = 2014
        and a.oprid = 'A1111111'
      ORDER BY supervisor_id,10,2, 8, 3, 4, 1, 5"])
    in
        JEStatus

    I have tried substituting the 2 with :vPeriod @vPeriod %vPeriod even tried typing in Parameter{0}[Value].

    Thank you for all your help in advance. I feel like it is something very minor that I am missing.

    Saturday, April 5, 2014 12:30 AM

Answers

  • In the Power Query formula language, the & operator is used to concat two text values. For your query string, just use it as such:

    ...A.ACCOUNTING_PERIOD =" & vPeriod & "and a.fiscal_year = .......

    Does that work for you?


    Zafar Abbas

    • Proposed as answer by Zafar Abbas Saturday, April 5, 2014 9:00 PM
    • Marked as answer by Micheal Reynolds Sunday, April 6, 2014 10:47 AM
    Saturday, April 5, 2014 2:07 AM

All replies

  • In the Power Query formula language, the & operator is used to concat two text values. For your query string, just use it as such:

    ...A.ACCOUNTING_PERIOD =" & vPeriod & "and a.fiscal_year = .......

    Does that work for you?


    Zafar Abbas

    • Proposed as answer by Zafar Abbas Saturday, April 5, 2014 9:00 PM
    • Marked as answer by Micheal Reynolds Sunday, April 6, 2014 10:47 AM
    Saturday, April 5, 2014 2:07 AM
  • We don't currently support passing parameters when you use an embedded SQL statement like that. Ideally, you'd build this query using Power Query by going directly against the Oracle tables inside the PQ query builder and let Power Query generate the actual SQL statement. But you can make this approach work in one of two ways:

    1) Construct the SQL query programmatically the way Zafar describes. This has the drawback that the user will be prompted for each individual parameter value that's used to construct the query.

    2) Perform the filtering in a second step that's not part of the Oracle query. This has the drawback that all of the data needs to be fetched and filtered locally instead of being filtered on the server.

    Implementing parameter support for embedded SQL statements is fairly easy to do naively, but we haven't yet found an approach that adequately satisfies our concerns about security.

    Sunday, April 6, 2014 3:44 AM
  • Zafar,

    Your answer got me to thinking differently and lead me to this solution: (I actually added more variables too):

    let
     Parameter = Excel.CurrentWorkbook(){[Name="TParam"]}[Content],
     vPeriod=Parameter{0}[Value],
     vYear=Parameter{1}[Value],
     vAcid=Parameter{2}[Value],

     JEStatus = Oracle.Database("EFPROD01", [Query="SELECT A.BUSINESS_UNIT as BU, A.JOURNAL_ID as JE,
       a.descr as Name, TO_CHAR(A.JOURNAL_DATE,'YYYY-MM-DD') as JEDate, a.attachment_exist as Att,
       A.SOURCE as Src, A.JRNL_HDR_STATUS as Status, A.JRNL_PROCESS_REQST as Rqst,
       A.OPRID, b.oprdefndesc as Employee, c.supervisor_id as Approver, A.DTTM_STAMP_SEC
     FROM SYSADM.PS_JRNL_HEADER A, sysadm.PSOPRDEFN b, sysadm.ps_CBI_GL_APPROVAL c
        WHERE a.oprid = b.oprid
        and a.oprid = c.oprid
        and A.JRNL_PROCESS_REQST IN ('G','N','E')
        AND A.JRNL_HDR_STATUS NOT IN ('D','P','U')
        AND A.LEDGER_GROUP = 'ACTUALS'
        and A.ACCOUNTING_PERIOD = " & Text.From(vPeriod) &  "
        and a.fiscal_year = " & Text.From(vYear) & "
        and a.oprid = '" & vAcid &"'
      ORDER BY supervisor_id,10,2, 8, 3, 4, 1, 5"])
    in
        JEStatus

    As a little bit of explanation for others, the first section after the "let" statement, is pulling my variables from a named table (TParam) within the excel spreadsheet. The table has two columns, the first being a user friendly name and the second column entitled "Value" holds the data that I am inputting. The numbers within the "{}" indicate the rows under within the table where the data is found.

    The second section, I used the concatenation as you suggested but I had to convert numbers to text. That is why there is a "Text.From" (case sensitive) included with the values. I may experiment with moving that up to the first section to see if that would work as well.

    Thank you.


    Sunday, April 6, 2014 11:01 AM
  • I'd suggest to make your parameters name based instead of index based. I use this function

    let
        GetReportParam = (param as text) =>
    let
        raw = ( Table.SelectRows(Excel.CurrentWorkbook(){[Name="Parameters"]}[Content], each [Name] = param) ) {0} [Value],
        term = Text.From(raw)
    in
        term
    in
        GetReportParam

    Sunday, April 6, 2014 5:11 PM