Update sql query parameter from excel cell

[glebret]

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.

 

[Ron Coderre]

First, I have a Power Query function named: fnGetNamedCellVal

(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

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

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:

Select *
    From myServer.MyDatabase
    where Region is not null

Is that something you can work into your code?

 

[gerald.lebret]

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