Update sql query parameter from excel cell

glebret

New Member
Joined
Sep 16, 2015
Messages
13
Hi,

I have a sql query stored in power query.
I have managed to change some of the settings of that query with values from the excel sheet.
For instance I stored a value 1, or a text chain in excel cell so that I can update my query without using VBA.

However, some of the parameters I need to change have the value "is null" or "is not null".
Can anyone tell me how to do?
I would like to create a drop down list with isnull or is not null and when I change the value to one or another one, it updates my sql query.

Thanks for your help.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
First, I have a Power Query function named: fnGetNamedCellVal

Code:
(ParameterName as text) =>
let
     ParamSource = Excel.CurrentWorkbook(){[Name=ParameterName]}[Content],
     Result = ParamSource{0}[Column1]
in
Result


This Power Query script

- Reads SQL text from a range named rngSQL
Code:
Select *
    From myServer.MyDatabase
    where Region is paramregion

- Reads a parameter from a named cell: paramregion
paramregion: not null


- Inserts the param into the SQL text

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="rngSQL"]}[Content],
    Table2List = Table.ToList(Source),
    ConcatSQLtext = Text.Combine(Table2List,  " "),
    Region = fnGetNamedCellVal("paramregion"),
    PutRegionInSQL = Text.Replace(ConcatSQLtext, "paramregion", Region),
in
    PutRegionInSQL

In that example the updated SQL is:
Code:
Select *
    From myServer.MyDatabase
    where Region is not null

Is that something you can work into your code?
 
Upvote 0
Hi Ron,

I have tried to send you a private message. It looks exactly like what I need. However, I am probably missing some steps. I was wondering, since I have 4 parameters I would like to dynamically change, if I could send you the file I am using and if you could do it for one of the parameters.
Thank you very much for your help.

Best Regards
Gerald
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top