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.

alma_pi · Accepted Answer · 2021-05-12 16:08:44Z

3

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

edited May 12, 2021 at 16:08

answered May 12, 2021 at 14:41

alma_pi

517 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

alma_pi Over a year ago

This comment should really go somewhere else, but I don't have the reputation to do that. The question is related to link - at least subject wise. Maybe the answer is different nowadays because Excel has changed since then....

Sharif · Accepted Answer · 2020-05-08 07:47:36Z

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 ),

answered May 8, 2020 at 7:47

Sharif

3223 silver badges13 bronze badges

1 Comment

Aspiring Developer Over a year ago

Where does the first block go exactly ? Do I add the "params = " and the "code = " in the top of the SQL Query ?

Collectives™ on Stack Overflow

Powerquery - reference Excel cell value within SQL query?

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related