Powerquery data queries kill Excel (32bit)

[MartinL]

HI
I have a workbook with 4 Power Queries all of them pretty substantial 130,000 rows each and 2 of them grouped to 4000 rows and 800 rows - hope that makes sense.

If I refresh them manually 1 by 1 no problem
If I attempt Refresh All it starts and then hits a limit and bombs out - Excel just shuts down

Only 2 of the Queries need refreshing daily so I tried to uncheck Refresh this connection on Refresh All on the others - still bombs out
I tried this on just one of them - the most important data set and the largest it still bombs out

This dataset also has 432 pivot tables attached to it, across 24 worksheets - Mental I know.
FYI I'm pulling in the dataset as a connection only
Cleaning it to 811 rows which is dumped onto a worksheet
The pivot tables are then built off this worksheet

As I said if I refresh manually one at a time no problem it only causes problems if I Refresh All
Any ideas of a work around or how to fix maybe so the data loads are only consecutively instead of simultaneously
Using 64bit is a non starter - company policy

 

[RoryA]

Assuming you can't redesign the workbook, I'd suggest you try coding a specific refresh of each query, then a refresh of all the pivotcaches, and see if that helps.

 

[MartinL]

Assuming you can't redesign the workbook, I'd suggest you try coding a specific refresh of each query, then a refresh of all the pivotcaches, and see if that helps.

Hi @RoryA
Many thanks for your reply.
I looked into some VBA to do just that and it worked!

Sub RefreshPowerQueries()
Dim con As WorkbookConnection

For Each con In ThisWorkbook.Connections
    If InStr(1, con.Name, "Query -") Then
        With con.OLEDBConnection
            .BackgroundQuery = False
            .Refresh
        End With
    End If
Next
End Sub

Then I noticed the line .BackgroundQuery = False
So I unchecked Background Query and tried Refresh All again - It bombed out even though the queries started to run one at a time, funny I thought I had it sussed.

I then added the Pivot table refresh code as a CALL inside the end of Sub RefreshPowerQueries after the Next statement

Public Sub refreshPT()
    Dim ws As Worksheet
    Dim pt As PivotTable
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next
    Next
End Sub

Now it runs but can be temperamental ie the memory will not allow it to be run twice, it seems to lock the memory with the something

 

[RoryA]

You shouldn't really need to refresh each table individually - just refresh the caches:

For each pc in thisworkbook.pivotcaches
pc.refresh
next

 

[MartinL]

Ug

You shouldn't really need to refresh each table individually - just refresh the caches:

For each pc in thisworkbook.pivotcaches
pc.refresh
next

Ughhh
Just plugging your code in failed on pc.refresh
Reference isn't valid

So I though maybe a Dim was required to I added
Dim pc as PivotCache - Nope

how about
Dim pc as PivotCaches - Nope again

Am I missing something?

Public Sub refreshPT()
'    Dim ws As Worksheet
'    Dim pt As PivotTable
'    For Each ws In ThisWorkbook.Worksheets
'        For Each pt In ws.PivotTables
'            pt.RefreshTable
'        Next
'    Next

Dim pc As PivotCache
For Each pc In ThisWorkbook.PivotCaches
pc.Refresh
Next

End Sub

 

[RoryA]

That suggests you have something wrong in your workbook to me. Can you just try this version and see what happens:

Public Sub refreshPT()

On Error GoTo err_handler
Dim pc As PivotCache
For Each pc In ThisWorkbook.PivotCaches
pc.Refresh
Next

Exit Sub
err_handler:
   MsgBox pc.SourceData
   
End Sub

 

[MartinL]

Public Sub refreshPT() On Error GoTo err_handler Dim pc As PivotCache For Each pc In ThisWorkbook.PivotCaches pc.Refresh Next Exit Sub err_handler: MsgBox pc.SourceData End Sub

Interesting - that brought back a non existent datasource - Thanks @RoryA
I'll have to find where that is

 

[MartinL]

H

That suggests you have something wrong in your workbook to me. Can you just try this version and see what happens:

Public Sub refreshPT()

On Error GoTo err_handler
Dim pc As PivotCache
For Each pc In ThisWorkbook.PivotCaches
pc.Refresh
Next

Exit Sub
err_handler:
   MsgBox pc.SourceData
 
End Sub

Hi @RoryA
OK found the offending PT's and pointed them to the correct datasource
I'm still getting the memory issue though on using refresh PivotCache so now I'm completely stumped as Excel is still dropping stone dead and closing.
The initial file takes up 500Mb Memory (62% with the platform and company bits running too)
The refresh makes the file grow to 2.6GB (93%) before says enough is enough and just closing down.