Powerquery - reference Excel cell value within SQL query?

Question

I am trying to modify an excel worksheet that was given to me, with a connection to a SQL database that looks as follows:

select * from DB.AccountAssignments where Company_Code = '102'

How can I replace the static 102 value to reference a specific cell in Excel? For example, cell A1? The objective of course, being that when I change the 102 for a different value, the query will re-run without having to enter PowerQuery to edit the query it self each time.

NOTE: I found examples that involve filtering AFTER the query runs and pulls the data. However, the data source is HUGE so I need the Company Code parameter to be embedded within the query, so that the data brought into Powerquery is already filtered. That makes the difference between the query taking 5 seconds to run to 5 minutes to run.

I spent a couple hours attempting instructions on older posts, but have not been successful so far.

Answer 1

Convert cell A1 to Table from Range (Insert -> Table) Name the table as param (or anything you like) Use the below lines to use this table in your power query:

params = Excel.CurrentWorkbook(){[Name="params"]}[Content],
code = params{0}[value],

Use this in your SQL query:

select * from DB.AccountAssignments where Company_Code = code

Most likely you will have something similar to below line in your power query editor:

Source = Odbc.Query("dsn=SQLDSN", "select * from DB.AccountAssignments where Company_Code = " & code ),

Answer 2

I had the same problem and it took me some time to understand how to implement Sharif's answer. So here are more detailed instructions based on the same idea:

• Convert your cell into a named range (Formulas > Define Name)
• Select the cell and go to Data > Get Data > From Other Sources > From Table/Range, which opens the Power Query Editor
• Right-click on your cell value in the little preview table in the editor and select 'Drill Down'
• Go to Home/File > Close & Load To (the 'To' is important here!). An 'Import Data' window pops up. Select 'Only create connection'.

Now you have a query parameter, that can be used in any other query.

• Open the power query editor for your SQL query (Edit)
• User the parameter in your query: Source = Odbc.Query("dsn=SQLDSN", "select * from DB.AccountAssignments where Company_Code = " & NamedRangeCompanyCodeFromCell )
• I got the error Expression.Error: We cannot apply operator & to types Text and Number. I solved that by changing the type of my query parameter: In the query of my parameter under Applied Steps > Changed Type, I changed Int64.Type to type text
• Most likely, you will also need to give permission to run this query

---

Instead of creating a separate query parameter, it can also be created on the fly within the SQL query:

• Open the Power Query Editor of your SQL query (Edit)
• Open the advanced editor (Home > Advanced Editor)
• Do all the necessary steps here, e.g.:

let
    Params = Excel.CurrentWorkbook(){[Name="myNamedRange"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Params,{{"Column1", type text}}),
    CodeFromCell = #"Changed Type"{0}[Column1],
    Source = Odbc.Query("dsn=SQLDSN", "select * from DB.AccountAssignments where Company_Code = " & CodeFromCell)
in
    Source