In this post I want to share my experience with refreshing data in Power Query and the way it affects loaded data to an Excel worksheet. I was loading sales data from SQL Server and I noticed that there is no easy way to define a 1000 separator in Power Query. So I decided to do use the power of cell formatting in Excel. So I loaded the data to an Excel worksheet and I spent some time to do some cell formatting including thousand separation and currency formatting and so on. But, as soon as I refreshed the data source from Power Query all of the defined formatting were gone. I also spent some time to do some special formatting like 1000 separator or dollar sign directly in Power Query, but, it seems Power Query team in Microsoft didn’t consider formatting as a priority yet. Even with setting up locale and language settings in Power Query you cannot define the 1000 separator formatting directly in Power Query. As I needed the formatting features included in the solution I should have resoled the problem anyhow.
Let’s start with loading some simple data from SQL Server using AdventureWorks2012 database into Power Query. I’ve used “Production.Product” table to show you how Power Query treats a column with “Money” data type.
· Open Microsoft Excel and go to Power Query tab
· Click on From Database-> From SQL Server Database
· Put “Server” and “Database” then put the following code in the SQL Statement, then click OK
select
Name
, ListPrice
from [Production].[Product]
where ListPrice>=1000
· Here we go. List price is a Money column in the Production.Product table. As you can see in the below image, Power Query considered it as a Decimal number. And as you also can see there is no formatting you can do for the “ListPrice” column.
· Close and load the query to a worksheet, then change the format cell of “ListPrice” column as currency.
· Select Properties from Connections ribbon from DATA tab in Excel and tick “Preserve column sort/filter/layout” option for the “Power Query – Query 1” connection and click OK
Now go and add a new record in the Production.Product table in SQL Server then refresh the Power Query. As you can see the new added record is loaded to the worksheet and the formatting still remains.
Easy peasy!
Alternatively, and perhaps easier… you could’ve taken the PowerQuery, loaded it into a PowerPivot model (right there within Excel), and defined its formatting.
I did this and the formatting still changed in the 2016 ProPlus. The above option seemed to work for me.
If the issue is that column headers are changing, I was able to preserve formatting by “using headers as first row” for the final table. This will issue generic unchanging column headers. After you load the table, you can then hide the generic, unchanging table headers by unchecking the headers box in the table formatting section. You can then format the viewable table as you desire and will remain preserved after refreshing.
Any way to preserve the column width as well?
How to you preserve other formatting such as Cell color and text type on total ( i.e. BOLD) etc.
PowerQuery does not resolve number format, as it is not intended for such purpose. Currency data type is not related to specific formatting, but to decimal with 4 digits after coma (fixed).
One tip: When you are loading data from SQL Server, it is not the best idea to execute SQL statement directly in connection string. This blocks PQ potential, to fold query into native query. Instead better option in most cases is to establish connection to SQL database in single PQ query, then reference that connection in one or many child queries. Most of the transformations done will result in native SQL query executed against server, which may greatly improve performance. Be careful with early data type conversion, especially to Currency type, which does not have direct equivalent in SQL translated query. First step, that PQ cannot translate into SQL, will result in stopping translation for remaining steps.
To check if step was translated into SQL, right click on step amd check if menu item “View native query” is active. When active, you can see with that option the query preview.
Hi Dawid,
Welcome to BIInsight.com and thanks for your input.
It is interesting to see such an old blogpost still grasps some attention.
So, thanks for reminding me of such an old post.
About the second part of your comment:
It is indeed possible to force the query to fold when we’re using T-SQL using
[EnableFolding = true]. In some cases, we do not have access to the underlying database to create views; therefore, using T-SQL queries is a viable option.Read more about forcing query folding in Chris Webb’s blog.
Again, thanks for sharing your thoughts.
Cheers