Forum Discussion

John Murray's avatar
John Murray
Copper Contributor
Aug 07, 2017
Solved

How do you do a parameter query when not using Microsoft Query?

I have a workbook that uses the ODBC interface part of Microsoft Query to query a DB2 database.  The workbook actually queries over 20 different tables.  I have one cell in the workbook that is the m...
BI & Data Analytics
Parameter Query
  • Wyn Hopkins's avatar
    Wyn Hopkins
    Aug 12, 2017
    Hi John,

    Keep going it's worth it!

    To address your first point, Close and Load to... Connection Only. Those are actually 2 separate steps. In your screen shot your Load To.. is greyed out as you must have already loaded that query once and now you were editing it. In that case you did the right thing and used the Right Click option to access Load To... Connection Only

    On the second issue you should (I think) resolve this by going to New Query - > Query Options - > Privacy > Always Ignore Privacy Level settings. There's a hyperlink there as well if you want to find out more about what turning off Privacy warnings does, however it is fairly standard practice to set it to Always Ignore (but find out about it and make your own call :)



SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 07, 2017

Hi John,

 

I didn't work with DB2, just my guess if that could work

 

Source=DB2.Database("server", "database", Query=["SELECT something" & pFromMyCell])

where pFromMyCell is query/function which returns the string from your cell

John Murray's avatar
John Murray
Copper Contributor
Aug 11, 2017

Thanks for the tip, but I'm struggling getting something to work. 

In my spreadsheet I've put in cell C1 the value "D1" and then in cell D1 I put the value "123456" for example. 

Now in the query I modified it to:

Source=DB2.Database("server", "database", Query=["SELECT * " & "WHERE COL1 = 'ABC' and COL2 = '" & pINDIRECT(c1) & "'" "])

 

I get this:
Expression.Error: The name 'pINDIRECT' wasn't recognized.  Make sure it's spelled correctly.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 11, 2017

    John, pINDIRECT or how you'll call it is the query which you shall generate yourself.

     

    In brief, within Excel give to your D1 cell name, for example, myParam. Stay on it, click in Ribbon Data->From Table/Range. In query editor convert column to Text and take the value from the cell, M script code will look like

    let
    Source = Excel.CurrentWorkbook(){[Name="myParam"]}[Content],
    AsText = Table.TransformColumnTypes(Source,{{"Column1", type text}}){0}[Column1]
in
    AsText

    After that you may add result to your query as ... ["SELECT * " & myParam & "...]"

     

    Wyn Hopkins published recently detailed post how to pick-up parameters from Excel cells into G&T, only i don't remember where. If Wyn is around hope he will give the link and/or comments. On the other hand you may find in other places.

     

    Please check before what you connection works without any parameters, just add your "123456" directly into the Query= string.

     

      • John Murray's avatar
        John Murray
        Copper Contributor
        Aug 12, 2017

        Thanks a bunch and I'm getting a lot closer.  However.... as I'm following your steps in step 5 you indicate to "Home - Close & Load - Connection only" - I don't see an option to close as "connection only".

        I'm on the current channel so my Office365 has been updated with latest Get & Transform stuff.

        Now I did finally get there by rmb on the saved query and select "Load to" which gave me this window where I could select "connection only":

        I then went and edited my SQL query to now be

        ....... AND KUNNR = '" & QueryKUNNR  & "') ORDER BY KTOKD .......

         

        When it tries running I now get this:

        Formula.Firewall: Query 'KNA1 by KUNNR' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

         

        As I said - oh so close now. 

Resources