Search code examples
powerbipowerquerym

Power Query Parametrization - Choose between loading a limited number of rows or a complete rowset using a parameter


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

Solution

  • 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.

    enter image description here