I have 3 queries to a SQL server in power query that load their data directly to the data model. I then have 3 pivot tables based on that data. Calculation mode is set to automatic.
Issue happens with the Refresh All button, as well as the macro below:
Sub RefreshReport()
'
' RefreshData Macro
'
Dim c As Range
Set c = Range("J2")
c.Value = "Start Refresh: " & Now
ThisWorkbook.RefreshAll
Dim s As Worksheet
Calculate
Set c = Range("J3")
c.Value = "End Refresh: " & Now
End Sub
I have also tried adding "s.PivotTables("name").PivotCache.Refresh" before or after the calculate step, but this only seems to rerun the queries that pivot relies on without actually updating the pivot table on the sheet.
There is a single slicer on all three pivot tables. I can get the pivot tables to refresh if I double click in them to drill down, but nothing else seems to do it and I don't want to macro the drill-down and the closing of resulting extra worksheets from drilling down just so my pivots show up properly for my users.
Has anyone experienced this when using power pivot and power query together?
(Also, to the forum admins here, clicking post, being told to correct any issues (i had forgotten to choose a label), correcting those errors, then clicking post again is not "post flooding". Should probably correct that.)
