Percentages Disappeared from Power Query

Introduction

I have been using Power Query (PQ) for quite a while now and it works like a dream. There are times when it falls apart, of course but usually it does what it should do.

I came across a problem the other day, though, when a project I was working on, using PQ, turned all of the percentage values in a table into zero. This is the story of the problem and the solution.

The Problem

I downloaded a series of tables of financial data relating to various companies and included in those tables were dollar values, decimal values and percentages. Of course, I have worked with such number many times both in ordinary Excel and in Power Query so I thought nothing of it. But then I noticed that all of the percentage values had been set to zero.

I thought that was odd and when I looked into it, I initially could not see why that would be the case. They were shown as percentages in the Excel Table/Raw Data, they were shown as percentages in the Query Editor. However, they were shown as zero in the unpivoted Query table.

Note: I have not included a screenshot of this problem: instead, I am concentrating on the steps I too to solve the problem and the final solution.

The Solution

The solution turned out to be the Data Type that PQ created when I initially converted the data in the Editor: they were all showing as ABC123 and yet they were a mixture of whole numbers and decimal numbers.

I stepped back through my query and changed the Data Type of 1.2 Decimal numbers, pressed Close and Load and the problem was solved. Notice in the screenshot that follows, I had some empty cells that Excel and PQ had sown as -. I needed to delete those dashes or they would show up as errors in the final Query.

Finally, here we see the columns changed to decimal numbers and the dashes shown as empty cells or as null. That is it, all is well now!

The proof of the pudding: instead of cells that showed only 0, the Sum of Value column shows the values as decimals now. As a matter of interest, I did not format the pivot table to show percentages because, as you can see in the slicer, there are 27 metrics, some of which are large whole numbers, some are decimals and some are percentages. I have left all values unformatted, therefore.

There is no file to download with this post as you can easily replicate what I am doing by using or creating your own file and query.

Duncan Williamson

5^th March 2020