Formatting Power Query Columns

Power Query is a great tool to quickly, and easily, load data into an Excel workbook. However, on the surface one potential drawback is the lack of formatting one can apply to the columns. This post will describe in further detail what I mean and show how to overcome this issue.

First, let’s take a look at how Power Query doesn’t work very well with formatting columns. Here I am pulling a set of data into Power Query and looking to format various columns with appropriate data types such as integer, currency, and date. I set the appropriate data types.

Whole Number

pq - whole number


pq - currency


pq - date

Now that we set the data types, do a Close & Load to load the data into the Excel worksheet. We can inspect each column that we formatted and will see that the Whole Number and Currency turned into a General format. However, the Date data type actually does come through as a Date format.

Whole Number

pq - whole number - after load


pq - currency - after load


pq - date - after load

Now let’s look at how we can actually format the number (integer), and currency columns. Yes, we can set it in Excel like normal but the problem is that when you refresh the data it will revert back to non-formatted columns. To get around this, click on the Data tab at the top and select Properties.

pq - data properties

Check the box that says ‘Preserve column sort/filter/layout’.

pq - preserve column layout

You can now format the columns in Excel like normal and it will retain formatting changes after data has been refreshed!

pq - format preserved

This works very well and you can format data just like normal in Excel. The only downside is that it is workbook specific. If you use the same query, possibly from the Power BI Data Catalog query, it will not retain the formatting changes and you will need to format each column again in every workbook you use.


One response to “Formatting Power Query Columns

  1. Does anyone find it odd that you need to check the “preserve column/filter/layout” to maintain the spreadsheet formatting when there is already a check mark in the box titled “Preserve cell formatting”? Wonder what that box is for.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s