Power Query: Power Query Tables Do Not Have to be Green

Welcome to our Power Query blog.  This week, we change the format of the Table loaded from Power Query.

I have some data for the manager for the upcoming sales conference:

The manager is happy with the data, but they want to know why some of the Tables are green.  You may realise that the default colour for input Tables is blue / white, whereas the default colour scheme for Power Query output Tables is green / white.

I can select each Table by clicking somewhere in it, and then choose the ‘Table styles’ option that matches the other tables (here, I have Table_Contains selected):

I do this for all the Tables, and choose ‘Refresh All’ from the Data tab to ensure that the formatting is preserved:

Some of the column widths have changed, but the formatting is fine.  We receive the news that Paul is sick, so we have to remove him from the Selection table:

I ‘Refresh All’ twice, once to update the Power Query Tables and again to update the PivotTable. 

Everything is fine, so we’ve solved the problem.  

Well, not quite.

The manager wants to add some location data, so I load the query to the same sheet:

When I click OK, the new Table appears:

The new Table is green.  This means I will need to alter the ‘Table style’ for every new query.  Power Query is supposed to save me from repetitive tasks!

There is another way.  I return to the original version of the sheet, and go to the ‘Page Layout’ tab:

The leftmost section is Themes. The Themes icon shows that my current theme is Office.  If I click on the ‘Colors’ dropdown, I can see all the Themes I can choose. 

The last colour in the Office palette looks familiar.  I choose ‘Customize Colors…’ 

‘Accent 6’ is the colour used for Power Query Tables. I change this colour to blue and save my new theme as ‘Power Query’:

This updates the Power Query Tables.

Now if I add the Locations Table again:

The new theme is used.  Currently, the theme will only be used for this workbook, but I can select it  from the ‘Colors’ dropdown when I am in other workbooks. 

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

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