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