Forum Discussion
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 solution yet. Anyone can help please?
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.⚡
I think this blog post might be able to help.
https://blog.crossjoin.co.uk/2020/07/05/why-is-power-bi-running-my-sql-query-twice/
6 Replies
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 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.
letSource = 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 = 0then #table(GetType(), {})else Table.FirstN(Source, count)])inOverrideZeroRowFilter
I think this blog post might be able to help.
https://blog.crossjoin.co.uk/2020/07/05/why-is-power-bi-running-my-sql-query-twice/
jdbuchanan71 thanks for sharing. more or less the same workaround that I posted.
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.⚡
Yep, I opened the reply then went to find the link which means I stepped on your reply. Sorry about that parry2k
