Power Query: Models Have Relationship Issues Too

Welcome to our new Power Query blog. In the last two blogs I established that Power Query is an ideal starting point to create the Excel model for use by Power Pivot, and I demonstrated how a table can be added to the Excel model and opened in Power Pivot. Today I look at whether changes to the model should be done in Power Query or Power Pivot.

Having started to refine the Excel model in Power Pivot, I am likely to find that changes to the model are necessary to facilitate the features that I want to add. The good news for users of Excel 2016 is that since Power Query has been fully integrated into the Get & Transform section, there is no choice between using Power Pivot or Power Query. In earlier versions of Excel 2013, and in Excel 2010, it is possible to corrupt the Excel model by making some types of changes in Power Pivot. The current version of Excel 2013 stops me from making these changes in Power Pivot. And that is a good thing.

Starting with the Excel model I created last time, I’ve decided to change the table name to make it more user friendly:

So, having received the message in the screen shown above, I am directed back to Power Query in order to rename the table. The reason that I am stopped from changing the name here is that in previous versions of Excel 2013 and 2010 users could change the name in Power Pivot which then broke the link between Power Query and Power Pivot. Therefore, there are some actions to be avoided in Power Pivot if the Excel model has been created in Power Query:

• Do not change a table name in Power Pivot
• Do not rename an imported column in Power Pivot
• Do not delete an imported column in Power Pivot

Any of these actions could result in a broken link between Power Query and Power Pivot.

It therefore makes sense to clean up data as much as possible in Power Query, renaming and deleting as required. Although merging some tables and queries in Power Query can be useful to avoid having unnecessary tables (more on this next week), don’t try to flatten all the data into one huge table. One of the features of Power Pivot is the ability to manage relationships between tables, allowing keys to be constructed as required. This is vital in creating accurate calculations and useful new columns to aid analysis of data. As with most relationships, Power Query and Power Pivot work well together when they are allowed to do what they are best at.

Next time I’ll take a look at merging tables and queries in Power Query…

Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!