7 thoughts on “Resolving Format Cells Change after Refreshing Data Sources in Power Query

  1. Alternatively, and perhaps easier… you could’ve taken the PowerQuery, loaded it into a PowerPivot model (right there within Excel), and defined its formatting.

    1. 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.

  2. How to you preserve other formatting such as Cell color and text type on total ( i.e. BOLD) etc.

  3. 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.

    1. 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:

      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.

      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

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.