none
"Refresh All" breaks pivots built from PowerQuery data RRS feed

  • Question

  • When I click "refresh all" in my workbook, the date timeline slicer for my pivots disappears. I have determined that the refresh occurs for all PivotTables immediately, while the PowerQuery download is still processing. Thus, the Pivots are refreshing from null (or incomplete) data.

    I tried to resolve this by writing a macro that refreshes the power query connection, then refreshes the pivots, but the same issue still occurs -- it does not wait until the data is downloaded before it refreshes the pivots built from the downloading data.

    How can I accomplish this so that my users can refresh the workbook to see the new query data and pivots built from it?


    Shawn Keene

    Thursday, July 10, 2014 2:34 PM

Answers

All replies

  • I'm having the same issue too, I'm able to refresh certain workbooks with powerquery fine as long as I first refresh powerquery table and then refresh all others but not simultaneously.
    Saturday, July 12, 2014 12:34 AM
  • Thanks for reporting this issue. There isn't a workaround at this point, but we'll work on enabling a better experience for this scenario.

    Wednesday, July 16, 2014 4:02 PM
  • Hi,

    I also have a request regarding this issue.

    I whould like to update and set a charts Y-values via macro

    after PowerQuery refresh. I have not found any way to run a macro automatically after PowerQuery refresh.

    Is this someting that can be added ?

    Best Regards

    Tony

    Tuesday, July 22, 2014 7:53 AM
  • I was previously trying this short macro to refresh the data, then the pivot, then set a date slicer to "yesterday".

    ActiveWorkbook.Connections("Power Query - AllShops").OLEDBConnection.BackgroundQuery = False
    
    ActiveWorkbook.Connections("Power Query - AllShops").Refresh
    
    ActiveSheet.PivotTables("AniShopPivot").PivotCache.Refresh
    
    ActiveWorkbook.SlicerCaches("NativeTimeline_StartDate").TimelineState.SetFilterDateRange DateAdd("d", -1, Date), DateAdd("d", -1, Date)

    The BackgroundQuery=False was successful in preventing the PivotTables from breaking, but the slicer I was using kept disappearing.

    I experimented with Application.Wait to delay the process without success.

    But now this appears to be working well: placing them in separate functions and calling them:

    Sub RefreshData()
        ActiveWorkbook.Connections("Power Query - AllShops").OLEDBConnection.BackgroundQuery = False
        ActiveWorkbook.Connections("Power Query - AllShops").Refresh
        Call RefreshPivot
    End Sub
    
    Sub RefreshPivot()
        ActiveSheet.PivotTables("AniShopPivot").PivotCache.Refresh
        ActiveWorkbook.SlicerCaches("NativeTimeline_StartDate").TimelineState.SetFilterDateRange DateAdd("d", -1, Date), DateAdd("d", -1, Date)
    End Sub


    Shawn Keene

    Tuesday, July 22, 2014 1:49 PM
  • Nevermind, I spoke too soon. It doesn't work.  The slicer disappears because when it tries to update, the data doesn't exist yet and it breaks.

    Shawn Keene

    Tuesday, July 22, 2014 1:50 PM
  • I had the same issue and here's how I worked around it.  

    I had slicers for the table that power query loaded into my spreadsheet.  My slicers would disappear when power query refreshed the data.  In power query I had used the Transform command to change the date/time fields to short date fields as the data was pulled by power query.  For whatever reason, this was the issue.

    To workaround this I simply allowed the date/time fields to come in as date/time fields and then performed the conversion to the short date format over in the worksheet table itself.

    Now the slicers perform as expected and remain visible when the data is refreshed.

    Thursday, January 1, 2015 3:59 AM
  • I was doing that too, but for some reason when the query refresh resulted in additional rows being appended to the table (which it does every morning because I refresh this report to append an additional day of data), the newly appended rows did not get formatted as date, even though the query has them as a date (and even if the last step of the query is to format the field as a date yet again, resulting in no apparent change but just to be sure it was).  I don't know what triggered it, but something in updating my Power Query version had done it to me and others.  At first I had asked all my colleagues not to update, but now that I know I can fix it by setting those table options, I am having them go ahead and update.


    Shawn Keene

    Monday, January 5, 2015 3:29 PM