none
Power Pivot Data model does not reflect change in Power Query RRS feed

  • Question

  • Relatively new to PP and PQ.  I have a Power Pivot data model with about a dozen tables joined together.  In the main data table (Loaded to the data model NOT workbook) there are many field but one of the fields is called Entity Code and it is joined to a table that contains the Entity Code and Entity description.  I pull the description into the pivot table.  The refresh of the data model worked great but need to change the Entity Code value in the main table from one value (113) to another (163) if the value of the Location field in the same main table was a certain value.  I have a step in query that make the update and I see in the Preview pane that the 113 was changed to a 163 properly.  Now when I go to the Power Pivot and do a Refresh All the Entity description does not reflect the 163 entity code but reflects the desc for the 113 entity code.  It's like the step that I added in PQ didn't flow into the PP data model.  I've done some searching and have seen some folks that had some problems with PQ changes where they had to mess with the connection.  Any ideas?

    thank you for any help,

    Eric

    Wednesday, February 22, 2017 10:32 PM

Answers

  • Turning off querying in the background forces each refresh to complete before the next query is refreshed. So:

    Public Sub RefreshConnections()
    
        With ThisWorkbook
            With .Connections("Connection1")
                .OLEDBConnection.BackgroundQuery = False
                .Refresh
            End With
            With .Connections("Connection2")
                .OLEDBConnection.BackgroundQuery = False
                .Refresh
            End With
            'and so on
        End With
    
    End Sub

    Friday, February 24, 2017 1:38 AM

All replies

  • You can use VBA to control the order of refresh. For example:

    Public Sub RefreshConnections()
    
        With ThisWorkbook
            .Connections("Connection1").Refresh
            .Connections("Connection2").Refresh
            'and so on
        End With
    
    End Sub

    Where "Connection1", "Connection2", etc. are the PowerPivot connections listed under "PowerPivot Connections" in the dialog that appears after clicking Existing Connections in the PowerPivot window.

    You can call this routine from a Workbook_Open event, or a button click on a worksheet.

    Thursday, February 23, 2017 4:40 PM
  • That didn't seem to work.  Does the order of the queries matter?  How does it handle value changes to a field that is the field linked to another table?  Does it establish the link after all the queried have refreshed?

    thanks,

    ERic

    Friday, February 24, 2017 12:22 AM
  • Turning off querying in the background forces each refresh to complete before the next query is refreshed. So:

    Public Sub RefreshConnections()
    
        With ThisWorkbook
            With .Connections("Connection1")
                .OLEDBConnection.BackgroundQuery = False
                .Refresh
            End With
            With .Connections("Connection2")
                .OLEDBConnection.BackgroundQuery = False
                .Refresh
            End With
            'and so on
        End With
    
    End Sub

    Friday, February 24, 2017 1:38 AM