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
    Moderator

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
    Owner
  • 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
    Moderator
  • Works great, but how do I display the data again (show all) if the cell is blank?
    Friday, October 03, 2014 4:22 PM
  • Hi Ben,

    I am new to Excel queries, and am learning them using Get & Transform on Excel 2016.

    Is there any chance you would mind describing the process again as it appears in Excel 2016.  I couldn't match up all the steps and new terminology.

    Thanks,

    Matt


    Thursday, December 28, 2017 11:07 AM
  • Thanks for trying to help. I must admit, that even though I think I know Excel and databases fairly well, I cannot get this to work as intended.

    • It looks like the terminology has changed, ther is no longer a Power Query and the menu items are different. Im am using Office 365 (Excel 2016).
    • There is no option to uncheck "My Table Has Headers" in step 3.
    • The option no longer says "Apply and Close"
    • Where is the "Filter Dropdown" you are referring to in step 8? It would be great with some screen shots. Is this a text filter?
    • Where is the query language that you are using in step 8 described? Why are you not using standard SQL?

    Again, step by step with screen shots would be great.

    I must admit that the query editor that Microsoft created in the 1990's was easier to use than this.


    Niels

    Monday, February 19, 2018 3:23 AM
  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Using multi-valued Table to get parameters for PQ.
    PP not used (yet).
    http://www.mediafire.com/file/k323kxggic4kker/02_09_18.xlsx
    http://www.mediafire.com/file/gl1i5tq5ybdp0bi/02_09_18.pdf

    • Proposed as answer by geraldartman Monday, February 19, 2018 5:50 PM
    • Unproposed as answer by geraldartman Monday, February 19, 2018 5:50 PM
    Monday, February 19, 2018 4:58 PM
  • I find using this user defined function easier.

    You can just name the cell in the workbook and use GetValue("my variable") wherever you want. It is doing all the same things, the array is returned and cell 0,1 is the value. I only use single value items typically. Examples are the folder path,  file complete path or perhaps a category to separate out of a bigger data dump.

    To create this function, choose Blank Query from the Other Sources menu and in the Advanced Editor mode past this over the default text and rename the query to GetValue or fGetValue to identify it as a function. In your queries I typically open the say folder first from the menu options and then replace the quoted file path with GetValue("my file location"). I keep the text below is Notes for quick reference. It would be nice to be able to create a PQ library of functions :-)

    let GetValue=(rangeName) =>
    let 
    	name = Excel.CurrentWorkbook(){[Name=rangeName]}[Content], 
    	value = name{0}[Column1]
    in 
    	value
     
    in GetValue
    


    Monday, February 19, 2018 6:00 PM