Forum Discussion

mq2020's avatar
mq2020
Helper III
6 years ago
Solved

SQL script runs twice after changing parameters

Hi, I am running a SQL script from power bi with user parameters. When the parameters are changed the script seems to be running twice.  I've seen some posts with similar issue but haven't found a ...
Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    mq2020 instead of having a script in pq, load view/table from the backend filter the table with a parameter, and with query folding it will send back the query to backend. 

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

parry2k's avatar
parry2k
Super User
6 years ago

mq2020 instead of having a script in pq, load view/table from the backend filter the table with a parameter, and with query folding it will send back the query to backend. 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

  • mq2020's avatar
    mq2020
    Helper III
    6 years ago

    parry2k  and jdbuchanan71 , thank you for your posts.

    It took me a while to get my head around it but I have now tested it and it seems to be working 🙂

     

    For reference to other users, I have added the following syntax to my query in the Advance Editor view and just make sure the data types were according to my own queries.

     

    let
    Source = Sql.Database("servername","databasename",
    [Query= "existingquerysyntax "]),
    OverrideZeroRowFilter = Table.View(
    null,
    [GetType = () => type table[
    Column1Name = Int32.Type,
    Column2Name = DateTime.Type,
    Column3Name = Byte.Type,
    Column4Name = Text.Type
    ], GetRows = () => Source, OnTake
    = (count as number) =>
    if count = 0
    then #table(GetType(), {})
    else Table.FirstN(Source, count)]
    )
    in
    OverrideZeroRowFilter