Adjust Power Query Table Style and Properties

How to adjust the Default Table Style and the Autofit Column Width Property manually and automatically for all Query Tables in a Workbook

960 words, ~5 minutes read

Very often, you get and transform data using Power Query and then load it into a table on a worksheet of your Excel workbook. When loading it for the first time, Power Query makes some decisions for you. Regarding the properties of the query table, but also regarding the used table style.

Most of theses decisions are fine. Especially two of them can become a pain in the neck, though.

First one is autofitting the column widths of the sheet to fit to the size of the header and the data. This is great, if you have only the table itself on the sheet, but it can be annoying, if you have also texts or calculations above the query table.

Second issue is the table design. Power Query uses the built-in Green Table Style Medium 7, not the default Table Style of the workbook.

I do not know of a way in Power Query to change these settings, so you always have to adjust your query table after the first load.

Today’s post describes how to do these adjustments manually for one query table and also provides a ready-to-use VBA code snippet to change properties and styles for all tables in a workbook in one go.

Issue 1 – Default Table Style used by Power Query

If you load the data of a query to a table on a worksheet in Excel, Power Query does not use the default table style set for this workbook. For whatever reason, it uses the built in Green Table Style Medium 7, even if you explicitly selected another style as default:

There is nothing wrong with this table style, but very often, I want to have another one. The manual fix is easy: select a cell inside the table, go to the [Table Design] tab of the Ribbon and select the style you want in the section Table Styles.

The annoyance: if you create another query and load it, you have the same issue again and there is no way to define the default Table Style in Power Query.

Issue 2 – Autofit the Column Widths

Per default, Power Query automatically adjusts the column widths of the table after loading it into a worksheet. This is well-meant, because it is helpful especially when you have only the table on a worksheet and nothing above it like some descriptive texts or cells with a calculation.

Here is one example: it may be just me, but I prefer to add SUBTOTAL calculations above the table to see aggregations on the filtered data:

I know of the option to add a total row to the table in the Table Style Options, but truth be told, I am not the biggest fan of this feature, because the total row is at the end of the table and to see the results, you always have to jump to the end. I prefer having SUBTOTAL calculations above the table and freeze the panes to always show the first 2 rows. By this, I can always see the results of the aggregation on the filtered data.

This turns into a problem, if you refresh the query, because when autofitting the column width, Power Query calculates the column widths only based upon the content in the table, i.e. the column header and the data. It does not care about the required width of the SUBTOTAL cell above the table. What you get is this:

You can manually resize the column width, of course, but you will have to do this after every refresh of the query. One work-around would be to insert the total row at the end of the table and use the same aggregation function as you do in the SUBTOTAL. Since the total row belongs to the table, the column width will be wide enough to show the number.

An even better option is to tell Power Query not to autofit the column widths anymore:

Select a cell inside the table, right click, click on |Table| and |External Data Properties|:

In the upcoming window, uncheck the box left to |Adjust column width|:

This solves the problem, but just as with the Table Style, the setting applies only to this specific query table. If you build another query and load it to a table, you have the same issue again.

Change the Table Style and the Adjust Column Width Property for all Query Tables

The issues described above count for little, if you have only a few query tables in your workbook, because the manual adjustments are quickly made.

However, if you have an Excel workbook using 20, 30 or even more queries loading to tables in worksheets (and I do have this situation in my project more often than you would think), the manual adjustments can become a pain in the neck.

To make things a bit easier, I wrote a little VBA routine which loops through all worksheets of the active workbook and sets the table style and the properties of all query tables as desired. If you are interested, here is a text file containing the code snippet:

Download Adjust Query Table Properties VBA (text file, 4K)

Download the file, open it with a text editor and copy the entire code. Next, open your Excel workbook, go to the VBE (ALT-F11), insert a module (ALT-I-M), paste the code in and run the sub (F5).

This is a one-off solution, i.e. once you ran the sub and double checked that it has done the job, you can delete the module in the VBE.

Stay tuned.