SQL script runs twice after changing parameters

[mq2020]

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? 

[parry2k · Accepted solution]

@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.⚡

[Icey]

Hi @mq2020 ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards,
Icey

[jdbuchanan71 · Accepted solution]

@mq2020 

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/

[parry2k]

@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.⚡

[jdbuchanan71]

Yep, I opened the reply then went to find the link which means I stepped on your reply.  Sorry about that @parry2k 

[parry2k · Accepted solution]

@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]

@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