duplicating table fails

[jja]

Hi

I have a stored procedure inside SQL that generates a #temptable

i can load this procedure intio powerbi and i get my table

for other task i need to unpivot this table and i tried to reference and duplicate the original table using powerquery editor

When refreshing dataset i get error on generating duplicate table that it can not find #temtable. IT seems the duplicate or reference or if i script directly to get the table copy using M anyway inokes sql procedure and i have no idea why it says it can not find this #temptable?

One strange thing i have in this setup is that when calling this procedure from powerbi in front i need to script drop #temptable else powerbi screams the object already exists.

What am i doing wrong in this scenario? maybe there is a way to clone and unpivot loaded table without calling the source sql procedure?

[Anonymous]

@jja 

Please share the code so we can see what happens in your model.

You may read this article see if helps: Chris Webb's BI Blog: Why Is Power BI Running My SQL Query Twice? Chris Webb's BI Blog (crossjoin.co...

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Jimmy801]

Hello @jja 

could you share some M-code and tell us where you are referencing your SQL twice?

BR

Jimmy

[jja · Accepted solution]

I found how to solve the issue. IT was wrong choice in sql procedure to use global temp tables. I switched to local temp tables and then i can run the procedure twice in data enquery