Power Query: Power Query Tables Don’t Have to be Green UPDATE

Welcome to our Power Query blog.  This week, I am interrupting our series on Project Population to being you a newsflash – sorry, I mean an update to a previous blog.

Regular readers will recall I described how the default colours of Power Query tables can be changed from the familiar green colour in Power Query Tables Don’t Have to be Green.  I started with a typical selection of Excel Tables, together with Tables created from Power Query: 

I changed their appearance by changing the Theme on the ‘Page Layout’ tab.  The green tables came from the Office Theme:

‘Accent 6’ is the default colour used for Power Query Tables:

I was able to change this colour to blue and save my new theme as ‘Power Query’:

This updated the Power Query Tables.

The theme would only be used for the current workbook, but I was able to select it from the ‘Colors’ dropdown in other workbooks. 

I could also share the saved THMX file with others, and set it as the default for all new workbooks if I wished.

However, there is now an easier way to ensure that Power Query Tables are created with the same colour as Tables created in Excel.  I can go back to my data, and set the Theme back to Office:

I edit any of my queries by accessing the ‘Queries & Connections’ from the Data tab, and double-clicking on a query:

In the File tab, I choose ‘Query Options’ from the ‘Options and settings’ dropdown:

In the dialog, on the General tab under GLOBAL, I can see a new setting:

‘Query table style’ can now be set to ‘Use default table style for query tables’!  I tick this box and click ‘OK’.  To test it, I create a new query by right-clicking on the current query, and choosing to create a reference query:

I call my new query Test_Colour, and choose to ‘Close & Load To..’ so that I can put it on the same sheet:

The new query Table starting in cell H3 is a reference of the query Table starting in cell A12, but it is clearly the same colour as the Excel Table starting in cell A3.  This setting will apply to any query Tables I create whilst the setting is on.  Changing the setting will not change existing Tables; you will need my original method for that. 

Come back next time for more ways to use Power Query!