locked
How to incorporate Excel cell value into where clause from external database in Power Query RRS feed

  • Question

  • Hi all, I have basic knowledge of SQL, and am working on a project that taps into a very large database, and I am trying to make it as user-friendly as possible for people without much excel or SQL background.

    I am trying to use an ID number within an excel cell, to feed into my query in Power Query that is currently pulling from an SQL database. My where clause has the ID number that I need pulled within it, but I want people to be able to update an excel cell and then refresh the table, rather than having to go in and edit the SQL itself.

    Is there a way to do this? I few people told me I need to use Visual Basic, but I dont have much experience with that nor M code.

    Wednesday, September 2, 2020 7:33 PM

Answers

All replies

  • Hi there. If you search for 'Power Query pass parameter to SQL' you'll find many examples of how to do this. Here's just one:

    https://channel9.msdn.com/Blogs/MVP-Azure/Pass-parameter-to-SQL-Queries-statement-using-Power-BI

    Ehren

    Friday, September 4, 2020 6:23 PM
  • That helps, but how can I get it so the parameter can be edited via an excel cell, so the end-user doesn't need to go into the Power Query screen?
    Friday, September 18, 2020 12:33 PM
  • Just import the value from Excel using "From Table/Range". Once in PQ, right-click on the cell you want to use as the param and select "Drill Down". Now you can reference this value by name, just like any other query.

    Ehren

    Friday, September 18, 2020 4:35 PM
  • When I try this, I get an error that states "Conversation failed when converting the varchar value 'Table cell' to data int type.

    The value that I want to use in the query is a text value. Not sure if that is causing my issue, or if it is because the source of the query is an external database, and the cell value's source is in excel? 

    Wednesday, September 23, 2020 5:19 PM
  • Can you share the M formula text for your query (copied out of the Advanced Editor)?

    Ehren

    Wednesday, September 23, 2020 5:23 PM