none
Power query, query with parameter from a cell

    Question

  • Hi everyone,

    I am working on Power Query and I have a question about using a parameter for the query of PowerQuery.

    I would like to know how i could use a cell in a worsheet as a variable into a request for sql ?

    Thanks


    Friday, March 28, 2014 2:55 PM

Answers

  • Here's a walkthrough of how to use a cell in an Excel table as a parameter for your SQL query.

    1. Type a value into an Excel cell
    2. Keep that cell selected. From the Power Query ribbon choose From Table.
    3. Uncheck My Table Has Headers before clicking OK.
    4. In the Power Query Query Editor, right click on that single value which you entered before and choose Drill Down
    5. Click Apply & Close
    6. In the Power Query, choose From SQL
    7. Connect to your data source (don't worry about using the parameter yet.)
    8. Once you're in the Query Editor with the correct SQL table being shown, choose the column you want to filter by your parameter. Go ahead and do a filter using the filter drop down. Now change the formula bar and replace the number that you filtered by with the name of the query that you created in Step 5. That query by default will be called Table1 so your query in this step might look something like this: = Table.SelectRows(dbo_MySqlTable, each [ID] > Table1)
    9. You will probably get a prompt asking you to classify the permissions to apply to each datasource to make sure there are no security leaks. Once you've done that, click OK.
    10. Click Apply & Close.

    You can prove that this works by going to the first sheet from Step 1 and changing the value. Then go to the last query in Step 9 and choose Refresh. Your parameter is now being used to affect your SQL query.


    Friday, March 28, 2014 9:23 PM

All replies

  • If this is an external workbook, you can use "From Excel" to access the sheets in the workbook.

    In order to access a cell from the workbook to which the query is attached, the cell needs to be part of a table -- though it could be a table with just one row and one column.

    Friday, March 28, 2014 3:07 PM
  • Yes I can create a cell where a list is created inside and change this cell of table?
    Friday, March 28, 2014 3:12 PM
  • Here's a walkthrough of how to use a cell in an Excel table as a parameter for your SQL query.

    1. Type a value into an Excel cell
    2. Keep that cell selected. From the Power Query ribbon choose From Table.
    3. Uncheck My Table Has Headers before clicking OK.
    4. In the Power Query Query Editor, right click on that single value which you entered before and choose Drill Down
    5. Click Apply & Close
    6. In the Power Query, choose From SQL
    7. Connect to your data source (don't worry about using the parameter yet.)
    8. Once you're in the Query Editor with the correct SQL table being shown, choose the column you want to filter by your parameter. Go ahead and do a filter using the filter drop down. Now change the formula bar and replace the number that you filtered by with the name of the query that you created in Step 5. That query by default will be called Table1 so your query in this step might look something like this: = Table.SelectRows(dbo_MySqlTable, each [ID] > Table1)
    9. You will probably get a prompt asking you to classify the permissions to apply to each datasource to make sure there are no security leaks. Once you've done that, click OK.
    10. Click Apply & Close.

    You can prove that this works by going to the first sheet from Step 1 and changing the value. Then go to the last query in Step 9 and choose Refresh. Your parameter is now being used to affect your SQL query.


    Friday, March 28, 2014 9:23 PM