none
Using a cell in Mysql Power Query RRS feed

  • Question

  • Good day all,

    I have a Mysql query in Excel 2013 where i want 1 value to be selected/used from a specific cell IE lets say B2.

    Currently the value is hard coded in as "35" as per the diagram. If i change cell B2 to value 25 for example, the query should use that value.. HELP !!

    Thursday, June 30, 2016 12:24 PM

Answers

  • 1) First create a query from the cell parameter. Essentially, you are creating a table with one column and one row.

    2) Convert the M generated code for the MySQL query into a function query. At the top of the query, just add a line like: (attendeeID as number) =>

    3) Modify the line, WHERE evyhc_jev_attendee.at_id = 35 to WHERE evyhc_jev_attendee.at_id = & Text.From(attendeeID). The entire SQL query, up to and including the equal sign, should be in double quotes.

    4) Add a custom column to the query in (1). The formula should be something like: 

    FunctionName([<columnName>])

    Tuesday, July 5, 2016 12:52 PM

All replies

  • Hi Garth,

    Search works really well. Take your pick from:

    https://social.technet.microsoft.com/Forums/en-US/home?forum=powerquery&sort=relevancedesc&brandIgnore=True&searchTerm=parameter

    Thursday, June 30, 2016 7:25 PM
  • Hmm.. thanks but not what after. I wasn't that clear agreed... and i can use search :) (keep getting this article)

    That solution downloads all the data from the table, then applies a filter to the table after all data is in, similar to using the top filter bar, just via a cell value instead.

    I have a MySql query that runs FIRST to fetch the data. Reason being is there could easily be 1 million entries there, but with the at.id filtered, i only actually fetch and download the records i want initially so only have to download from server maybe 100 records.

    So this dynamic cell value needs to be inserted into the SQl statement initially, not filtered after the data has arrived. Needs to be inserted into highlighted number without additional code (which would break the MySql query)

    

    Tuesday, July 5, 2016 12:11 PM
  • 1) First create a query from the cell parameter. Essentially, you are creating a table with one column and one row.

    2) Convert the M generated code for the MySQL query into a function query. At the top of the query, just add a line like: (attendeeID as number) =>

    3) Modify the line, WHERE evyhc_jev_attendee.at_id = 35 to WHERE evyhc_jev_attendee.at_id = & Text.From(attendeeID). The entire SQL query, up to and including the equal sign, should be in double quotes.

    4) Add a custom column to the query in (1). The formula should be something like: 

    FunctionName([<columnName>])

    Tuesday, July 5, 2016 12:52 PM