none
Does SQL statement "SELECT...FROM...WHERE...LIKE..." not support for data query connection of excel workbook? RRS feed

  • Question

  • As the title said, I want to use that statement to filter data query result but a warning pop out and said this SQL is not supported, same SQL used to filter result from ordinary ODBC connection does work, so is there some limit to use SQL statement to filter data query for Excel Workbook ? I have googled but find nothing about this.

    Thanks and looks forwards to some advice.

       
    Thursday, April 19, 2018 4:49 AM

Answers

  • Hey,

    That's pretty strange. The command text shouldn't appear like that. It should appear as the following:

    If you're following the steps exactly as showcased in the article and inputting your SQL command as shown in the next image:

    then you're most likely hitting a bug and I'd encourage you to get in touch with the Microsoft Power Query team via email: pq2fb@microsoft.com so they can find the origin of the issue. Sadly, I'm unable to replicate your error.

    In case you can, I'd encourage you to try to create the query from scratch again as showcased in the article provided previously and make sure that your command text is close to what you see on my image where it has the format of "SELECT * FROM [Name of Query]"

    Monday, April 23, 2018 5:11 AM

All replies

  • Hey!

    This article might help. If you're still receiving an error afterwards, would you mind running a trace on your server side to see what your server is saying and if it is indeed receiving the query? also, if you could share any screenshots of the error that Power Query is throwing your way, that would be phenomenal. 

    Thursday, April 19, 2018 5:51 PM
  • Hi Miguel,

    Thanks for your advice, and I have upload the warning windows and connection properties as below:

    I want to use the query to excel workbook because the query could be more powerful than ordinary odbc query, which could delete the first useless row and use the second row as table's header. If I use odbc query, I do not know how to do that.

    That's why I want to know is there any limit on SQL for a data query connection of excel workbook.

    Below it the trace log shared in onedrive:

    Trace Log

    It would be very appreciated to get more advice :-) 


    Monday, April 23, 2018 2:03 AM
  • Hey,

    That's pretty strange. The command text shouldn't appear like that. It should appear as the following:

    If you're following the steps exactly as showcased in the article and inputting your SQL command as shown in the next image:

    then you're most likely hitting a bug and I'd encourage you to get in touch with the Microsoft Power Query team via email: pq2fb@microsoft.com so they can find the origin of the issue. Sadly, I'm unable to replicate your error.

    In case you can, I'd encourage you to try to create the query from scratch again as showcased in the article provided previously and make sure that your command text is close to what you see on my image where it has the format of "SELECT * FROM [Name of Query]"

    Monday, April 23, 2018 5:11 AM