Text variable in Power Query

Question

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

Expression.Error: The name 'ReturnQuery' wasn't recognized. Make sure it's spelled correctly. — Przemyslaw Remin
Simple enough :) PowerQuery can do if's myonlinetraininghub.com/power-query-if-statements — Murray Foxcroft

Angelo Canepa Angelo Canepa · Accepted Answer · 2021-04-04T12:04:20

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.

Text variable in Power Query

1 Answers