I would like my SQL query in PBI to be defined by parameter. The Parameter1 is a list of two values Big
and Small
. If I set up Parameter1 to Small
then I want only a sample of data to be loaded. I am doing it this way:
let
ReturnQueryVar = (optional x) =>
if Parameter1 = "Small"
then " and Product in (1, 2, 3)"
else "",
Source = Sql.Database(
"myservername",
"mydatabase",
[
Query = "
select *
from table
where
1=1"
& ReturnQuery()
]
)
in
Source
I get this error:
Expression.Error: The name 'ReturnQuery' wasn't recognized. Make sure it's spelled correctly.
Update.
Facepalm! It was a typo. ReturnQuery should be ReturnQueryVar.
However, I would like to leave the question open for the part:
ReturnQueryVar = (optional x) =>
if Parameter1 = "Small"
then " and Product in (1, 2, 3)"
else ""
Does PowerQuery have a syntax for IF:
TextVar = IF(Parameter1 = 'S', 'This', 'That' )
I haven't seen syntax like that, but if you like you can create a function that does that, basically a "container" for the IF
statement of Power Query
.
For example, you can create a Blank Query
called IF
with the following contents.
(cond as logical, if_true as any, if_false as any ) =>
if cond then if_true else if_false
Using that function, anywhere it would have similar results and an IF statement.