Preserve Formatting in Power Query

Question

I cannot seem to preserve previously set formatting upon adding a new column in a Power Query.

Source table has values as percent with two decimals

I import into PQ and it applies what it thinks is proper formatting:

I change the formatting to what I want:

Pivot on the Date Column

Load it back to the worksheet

Note that the percent formatting is lost.

If I format this "results" table in Excel as percent with two decimals, and subsequently add "rows" (eg, a new entry in the Source with the same date but a different value) and Refresh, the formatting will be preserved.

But if the new entry requires a new column (eg a new date), the formatting of this new column, when loaded back to Excel, will be General and not Percentage.

The Properties for the Query are as below.

Is there a PQ solution for these issues (both the initial and "refresh" formatting)? Or must I resort to VBA with a Worksheet_change event?

Here is the M-Code for the Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Date", type date}, {"Value", Percentage.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-US")[Date]), "Date", "Value", List.Sum)
in
    #"Pivoted Column"

So, I know this is 3 years old, but I am having the exact same issue except with currency rather than percentages. Is there a known solution or is still a VBA workaround? — tormond, Apr 24 '22 at 18:21
@tormond I have not run across a solution, other than using a VBA event to do the Excel formatting. — Ron Rosenfeld, Apr 24 '22 at 18:26

score 1 · Accepted Answer · answered May 12 '19 at 01:47

I am seeing the same behaviour. Values of type Percentage display as percentages within the Power Query Editor, but load with General number formatting to the worksheet.

Bad workaround

If your situation allows it (i.e. maybe if you only want to reference/display the table's values and not calculate/compute/perform any numerical operations), you could change the percentage values to text. (Not really a solution and a pretty bad workaround as internal representation of values is changed.)

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    transformed = Table.TransformColumns(Source, {{"ID", Text.From, type text}, {"Date", each DateTime.ToText(_, "M/d/yyyy"), type text}, {"Value", Percentage.From, Percentage.Type}}),
    newHeaders = List.Distinct(transformed[Date]),  
    pivoted = Table.Pivot(transformed, newHeaders, "Date", "Value", List.Sum),
    stringifyPercentageColumns = List.Accumulate(newHeaders, pivoted, (tableState, currentHeader) => Table.TransformColumns(tableState, {{currentHeader, each Text.From(_ * 100) & "%", type text}}) )
in
    stringifyPercentageColumns

Okay workaround

As you've mentioned, applying number formatting via VBA might be necessary. QueryTable.AfterRefresh event seems more relevant (documentation and worked example) but requires a lot of setup code for such a simple task; so Worksheet_Change event might be quicker to set up.

Maybe somebody else can give you an actual solution, but I think the problem is with Excel not having a "default number format" (if such a concept even exists) for Power Query's Percentage type. So the problem isn't with Power Query but with Excel, but I'm just guessing and could be wrong.

Unrelated. Your M code seems to include three separate Table.TransformColumnTypes, but I think you only need one (see transformed expression in my M code).

Thank you for your thoughts. So far it seems that a VBA solution is inevitable. Unfortunate that this cannot all be done without VBA. And, as you surmise, text representations of percentage is not really acceptable. so far as the multiple `Table.TransformColumnTypes`, I'm pretty inexperienced with PQ. The code was generated by the UI and, since it demonstrated the point I was trying to make, I didn't try to clean it up. — Ron Rosenfeld, May 12 '19 at 02:09

Preserve Formatting in Power Query

1 Answers1