Power Query query not running in excel

M

msouppaya

New member
Joined
Mar 24, 2021
Messages
6
Reaction score
0
Points
0
Excel Version(s)
365 office
Hello,


I have a problem with a query under PQ. Let me explain :
I have an Excel 365 + PQ + SQL Server 2012 file
I have a query that runs fine under SSMS and in the PQ editor.
When I load it on Excel: it takes much longer to run and finally fails.
An option to enable/disable? An idea ? Thank you !! :pray2:
 
Tell us a bit more about this query... you say it runs fine in SSMS. Is it a SQL query that you placed into the SQL Statement section (under Advanced) when configuring the PQ connection to the database?

How many rows does the SSMS version usually return?
 
Hello thanks for your return,

Is it a SQL query that you placed into the SQL Statement section (under Advanced) when configuring the PQ connection to the database?
Click to expand...
Yes I configure it in the advanced options.

Tell us a bit more about this query
Click to expand...
I simplified my query to the extreme and anonymize it (no choice sorry).
Code: 
-- delete tables
DELETE a
DELETE b
DELETE c
DELETE d


-- populate database table a
INSERT INTO a  
SELECT t.col1, t.col2, t.col2, t.col3, t.col4,
FROM a_view t 
WHERE t.col1 IN (Select colx FROM table1 WHERE colx in (parameter FROM excel) --parameter in excel


-- populate database table b
INSERT INTO b 
SELECT t.col1, t.col2, t.col2, t.col3, t.col4,
FROM a_view t 


-- populate database table c
INSERT INTO c
SELECT t.col1, t.col2, t.col2, t.col3, t.col4,
FROM a_view t 


-- results 
INSERT INTO d
SELECT * FROM a
UNION
SELECT * FROM b
UNION
SELECT * FROM c
no problem so far, it works fine
I run my query to display the table
Code: 
SELECT * FROM d
and PQ deletes the contents of my table (I specify that the database table was well populated)
On the other hand, when I execute the query under SSMS my result table does not empty even if I refresh it under Excel. I conclude that it is a problem with PQ.

How many rows does the SSMS version usually return?
Click to expand...
it is variable with parameter in Excel. But it never exceeds 50000 rows.

Sorry for my english but i am french and i write in english thanks to google translate.

Thanks :happy:
 
So if I understand your SQL code correctly, you aren't actually returning any rows to SQL. You are simply using Power Query in place of SSMS to:
-Clear data from four tables
-Populate tables a, b and c with new data
-Populate table d with a union of a, b and c

And you're doing it all in a single query.

If I have that correctly, I feel like I need to tell you that Power Query was not designed for this purpose. It was designed to read data, not write. The engine they use is complicated, and can change the order of steps/commands in order to retrieve data in the most efficient way. I'm not sure if that is at play here, but it may be.

If I were trying to do this kind of manipulation, I'll be honest that I would not use Power Query, I would just write a VBA routine to get the job done. That way I could turn on transaction processing and make sure that everything takes place properly before committing it to the database. This approach would make me VERY nervous.

If I HAD to use Power Query, I would probable separate each major operation into it's only query, (Q1: clear old data, Q2: load table a, Q3: load table b, etc...) and then use VBA to trigger the refresh of each query, waiting for the others to finish first.
 
Hi Ken,

Ken Puls said:
So if I understand your SQL code correctly, you aren't actually returning any rows to SQL. You are simply using Power Query in place of SSMS to:
-Clear data from four tables
-Populate tables a, b and c with new data
-Populate table d with a union of a, b and c

And you're doing it all in a single query.
Click to expand...

you understand well.

If I were trying to do this kind of manipulation, I'll be honest that I would not use Power Query, I would just write a VBA routine to get the job done
Click to expand...
In fact, that's what I'm going to do. I wanted to do without VBA but it seems complicated to me

If I have that correctly, I feel like I need to tell you that Power Query was not designed for this purpose. It was designed to read data, not write. The engine they use is complicated, and can change the order of steps/commands in order to retrieve data in the most efficient way. I'm not sure if that is at play here, but it may be.
Click to expand...
I've realized that

I would probable separate each major operation into it's only query, (Q1: clear old data, Q2: load table a, Q3: load table b, etc...) and then use VBA to trigger the refresh of each query, waiting for the others to finish first.
Click to expand...
I will most likely do this.

Thanks !!
 
You must log in or register to reply here.
Back
Top