Results 1 to 5 of 5

Thread: Power Query query not running in excel

  1. #1
    Seeker msouppaya's Avatar
    Join Date
    Mar 2021
    Posts
    6
    Articles
    0
    Excel Version
    365 office

    Question Power Query query not running in excel



    Register for a FREE account, and/
    or Log in to avoid these ads!

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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,483
    Articles
    43
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Seeker msouppaya's Avatar
    Join Date
    Mar 2021
    Posts
    6
    Articles
    0
    Excel Version
    365 office
    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).
    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?
    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

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,483
    Articles
    43
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Seeker msouppaya's Avatar
    Join Date
    Mar 2021
    Posts
    6
    Articles
    0
    Excel Version
    365 office
    Hi Ken,

    Quote Originally Posted by Ken Puls View Post
    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 !!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •