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

Currency

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

Currency

Date

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.

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

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

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.