Forum Discussion

utkuacik's avatar
utkuacik
Frequent Visitor
2 years ago
Solved

power query sql filter with variable , value from excel cell

power query sql code variable filtery for my question. i searched a lot but i saw very complex answer.

 

my question is very simple, i am using power query in excel . İ AM filtering and receiving RESULT from database from column "ITEMNAME" with values only equal to "CAN". i just want to raplace constant string value with a variable . and this variable value will be taken from a cell in excel . is it possible to write such a code  in power query advenced editor  ? 

 

below do not take into considiration decleration "WHERE [ITEMNAME] =thisworkbook.worksheets("test").cells(1,1).value" . i know it is wrong. just wanted to explain my requirement . 

 

original code sql

SELECT
[ITEMNAME]

FROM [db].[test]
WHERE [ITEMNAME] ="CAN"

 

required code :

SELECT
[ITEMNAME]

FROM [db].[test]
WHERE [ITEMNAME] =thisworkbook.worksheets("test").cells(1,1).value

 

i will appraciate your answer, thanks indeed guys . 

best regards

 

 

 

3 Replies

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    2 years ago

    It's not very far off.

     

    SQL = "SELECT
    [ITEMNAME]

    FROM [db].[test]
    WHERE [ITEMNAME] ='" & thisworkbook.worksheets("test").cells(1,1).value & "'"

     

    • utkuacik's avatar
      utkuacik
      Frequent Visitor
      2 years ago

      thank you very much for your kind answer 

       

      i tried below as in your reply , received below error

      "DataSource.Error: Microsoft SQL: Unclosed quotation mark after the character string ' '."

      i tried below code first received error above   :

      and [PLANT]='" & ThisWorkbook.Worksheets("PROJE_BILGILER").Cells(6,"BO").value & "'"

       

       

      then i deleted quotation mark at the end of the line like below, i did not reveive error , but the RESULT  was empty. i tred also writing cell as number only lıke below but again no result. if i write not variable but consttant string like [PLANT]= "UTK" İT WORKS . but when i reference excel cell , like below , no error but no result 

      and [PLANT]= '" & ThisWorkbook.Worksheets("PROJE_BILGILER").Cells(6,"BO").value & "'

      and [PLANT]= '" & ThisWorkbook.Worksheets("PROJE_BILGILER").Cells(31,1).value & "'

       

      then i tried below code

      WHERE PLANT = '" & ThisWorkbook.WorkSheets("PROJE_BILGILER").Range("A31").Value & "'"

       

      received below error

      DataSource.Error: Microsoft SQL: An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
      Unclosed quotation mark after the character string ''.

      sorry to bother you but this subject is very important for me ,  and after 1 month search could not find a solution .

       

      looking for your kind reply sir

      best regards