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
Continue reading “Resolving Format Cells Change after Refreshing Data Sources in Power Query”