none
Dynamic querying RRS feed

  • Question

  • I want to build queries that reference other power query results or cells.  What I did so far was build a table with a year value and got the M code from querying that table.  I then tried to use that like this:

    let
    Parameter= Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Column1 = Parameter{0}[Column1],
        
    Source = Sql.Database("x.x.x.x", "databasename", [Query="select * from EffortPeriod where name like '%'" & Column1 & "'%'"])
    in
        Source

    Is anything like this possible.  It's only a big deal when I query Oracle as the tables I get from their are large and poorly indexed (or not documented to me).  THey aren't my tables so I just try to do what I can to create useful tools for my customers.

    Ken

    Thursday, September 4, 2014 3:55 PM

Answers

  • Used too many single quotes.

    let
    Parameter= Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Column1 = Parameter{0}[Column1],
        
    Source = Sql.Database("x.x.x.x", "databasename", [Query="select * from EffortPeriod where name like '%" & Column1 & "%'"])
    in
        Source

    Now off to see if I can get the way more complicated ones to work.

    • Marked as answer by Kennethj Thursday, September 4, 2014 7:15 PM
    Thursday, September 4, 2014 7:15 PM

All replies

  • Yes, this is absolutely possible and you look like you're on the right track. What error are you getting?

    You may find that enabling the Fast Combine option solves your problems (see http://office.microsoft.com/en-gb/excel-help/privacy-levels-HA104009800.aspx) quickly, but it's also worth reading up on privacy levels as doing this would mean the values in your workbook could be seen by people other than you (though you may not care about that).

    HTH,

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Thursday, September 4, 2014 4:01 PM
  • I'm getting this:

    Expression.Error: Cannot apply operator & to types Text and Number.
    Details:
        Operator=&
        Left=select * from EffortPeriod where name like '%'
        Right=2014

    I also tried + instead of &, similar error.  I enabled fast combine and it didn't help.  Privacy should be fine as I'm building this for very local users and I can tell them where to store and use the files.  And as long as just the data is somewhat visible and not open to other data pulls then this would all be condensed data and not a huge confidentiality issue (IE not social security or similar and nothing tying to a specific user)

    Thursday, September 4, 2014 5:07 PM
  • This looks like a type conversion problem. Your parameter is a number (as far as I can see) and the & operator can only be used with text, so you need to convert the number 2014 to text. I think wrapping the parameter value in the Number.ToText() function (http://office.microsoft.com/en-gb/excel-help/number-totext-HA104110114.aspx?CTT=5&origin=HA104122363) should do the trick, or it might be even easier to set the type of the Column1 column of your Excel table to text by highlighting it in the Query Editor window then, on the Transform tab, select Text in the Data Type dropdown box.

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Thursday, September 4, 2014 6:59 PM
  • Used too many single quotes.

    let
    Parameter= Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Column1 = Parameter{0}[Column1],
        
    Source = Sql.Database("x.x.x.x", "databasename", [Query="select * from EffortPeriod where name like '%" & Column1 & "%'"])
    in
        Source

    Now off to see if I can get the way more complicated ones to work.

    • Marked as answer by Kennethj Thursday, September 4, 2014 7:15 PM
    Thursday, September 4, 2014 7:15 PM