CODEWITHSUNDEEP
sqlexcelpowerquery
ThiagoC
ThiagoC

Reputation: 71

PowerQuery - Using a cell in a table as part of the code in a query (dynamically or not)

I am trying to use a cell as a parameter in Excel powerquery. The query works without this, but I have to manually input the values, which I need to constantly change them in the query in other to get the results that I want.

Query (Advanced Editor):

let
    Criteria01 = Excel.CurrentWorkbook(){[Name="Servers"]}[Content][ServerSearch]{0},
    Criteria02 = Excel.CurrentWorkbook(){[Name="Servers"]}[Content][ServerSearch]{1},
    Criteria03 = Excel.CurrentWorkbook(){[Name="Servers"]}[Content][ServerSearch]{2},
    Source = Sql.Database("SERVERNAMEHERE", "DATABASENAMEHERE", [Query="SELECT DISTINCT [...........] AND (TABLEPREF.COLUMNHERE like '%MANUALVALUE01%' OR#(lf)TABLEPREF.COLUMNHERE like '%MANUALVALUE02%' OR#(lf)TABLEPREF.COLUMNHERE like '%MANUALVALUE03%' OR#(lf)TABLEPREF.COLUMNHERE like Criteria01)#(lf)#(lf)#(lf)order by 1 asc"])
in
    Source

"Servers" is the table name and "ServerSearch" is the column header. If I check the step for Criteria01/etc it will show me the correct value of that table that I need to use.

Original query done in Sql-Server. I have no problems when running the query with only LIKE '%MANUALVALUES%' lines.

My main goal is to automatically get N values of "MANUALVALUES" from a table in a sheet, which will be used as an input for comparing WHERE TABLEPREF.COLUMNHERE like '%VALUEHERE%'. I must use this and I can't get the whole table/database because there are way too many results besides the ones that I want.

However for test purposes at this moment, I am trying to use only 1-3 values, the first 3 of this table (Criteria{0}{1}{2} in the query above). However, if I try to do something like TABLEPREF.COLUMNHERE like Criteria01 I get the following error:

DataSource.Error: Microsoft SQL: Invalid column name 'Criteria01'.
Details:
    DataSourceKind=SQL
    DataSourcePath=dalsql390;itdw
    Message=Invalid column name 'Criteria01'.
    Number=207
    Class=16

So my questions are:

  1. I am getting the table cell value by the right way? Meaning: Excel.CurrentWorkbook(){[Name="Servers"]}[Content][ServerSearch]{0}.

  2. How do I refer this value in my query? Since the way that I wrote that query bought me that error.

    Also please note that if change TABLEPREF.COLUMNHERE like Criteria01 to CHG1.CI_Name like "Criteria01" I get the following error:

    Expression.SyntaxError: Token Comma expected.
  3. After fixed 1 and 2, how can I use this dynamically? For example, instead of getting values of index 1 2 3, what if I want to use a whole table? I know that using Excel.CurrentWorkbook(){[Name="Servers"]}[Content] will bring me the whole table of values (1 column, unknown number of rows), but how do I use this table content 1 by 1 in my query?

Upvotes: 2

Views: 667

Answers (1)

Alejandro Lopez-Lago - MSFT
Alejandro Lopez-Lago - MSFT

Reputation: 4134

That will get the value, but you can't refer to steps inside of text values by putting the step name inside of it.

You have a couple options for doing this dynamically.

  1. Use Value.NativeQuery to create a parameterized query where you can pass in other values as parameters. For example, Value.NativeQuery(Sql.Database("SERVERNAMEHERE", "DATABASENAMEHERE"), "select @a, @b", [a = 1, b = "x"]) will return the table [1, x]. You can put in the step name in the record value to pass that it (e.g. replace "x" with Criteria01).
  2. Add the text values directly in the query field, e.g. [Query = "select " & Criteria01 ";"]. This is highly discouraged since this can lead to SQL injection issues.

For the third question, it depends what you want to do with the list of values. At some point you will likely need List.Accumulate to turn them all into a single text value which can be placed in the query value, and maybe to turn them into a record to place into the parameters value.

Upvotes: 1

Related Questions