Power Query query not running in excel

[msouppaya]

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 !!

[Ken Puls]

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?

[msouppaya]

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?

Yes I configure it in the advanced options.

Tell us a bit more about this query

I simplified my query to the extreme and anonymize it (no choice sorry).

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

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?

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

[Ken Puls]

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.

[msouppaya]

Hi Ken,

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.

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

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.

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.

I will most likely do this.

Thanks !!