Pivot table results in scientific notation value that converts to 0. 1

[raven4]

I have data that I am exporting from an application in CSV format. I'm creating a pivot table with that data and for one larger set of values the pivot table is converting the resulting value to scientific notation. When I try to format the cell to change it to numeric it sets the value to zero.

Here's an example of the pivot table result:

Sum of Amount Months Entry Date
<1/1/2021
Account Title Account no Line of Business ID Location ID Months Jan Feb Mar
Revenue 4000 DEPT00200 LOC0101099 -1.5134E-09 -2.09548E-09 -3.46336E-09

The raw data looks like this (these are just a few rows):

Account title Account no Line of Business ID Location ID Entry date Amount
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -2312.04
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -105.60
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -25.22
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -1460.57
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -1105.00
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -6526.08
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -112.32
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -2796.22
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -401.51
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -1953.75
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -1440.00
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -829.92
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -276.00
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -168.96
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -2673.60
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -460.00
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -50.00
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -474.20
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -211.12
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -289.55
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -25.00
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -2370.96
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -847.20
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -7966.91
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -633.60
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -4037.22
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -566.12
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -54607.95
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -1020.00
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -94.85
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -1905.30
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -3240.15
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -31202.00
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -80.19
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -262.08
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -2431.14
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -14521.14
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -833.96
Revenue 40000 DEPT00200 LOC0101099 1/5/2021 -180.00



I've scoured a few posts in the forums here and followed the suggestion NOT to open the CSV file but to import it as external data. I've tried that, along with converting the cells in the raw data to numeric before creating the pivot table but no matter what I try it results in the scientific notation and converting that results in zero.

Office 365.

Thanks.

 

[SkipVought]

Hi,

So I assume, from your recounting the urgings to IMPORT, that you imported the data from the .csv file and designated the Entry date as YMD and Amount as General and everything else as Text.

[pre]
Account title Account no Line of Business ID Location ID Entry date Amount
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -2312.04
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -105.60
Revenue 40000 DEPT00200 LOC0101099 1/4/2021 -25.22
...
[/pre]

Notice that when you IMPORT, the resulting table is a Structured Table.

So why didn't you change the Amount column Number Format as Currency? That may give you the results you want. I assume that you imported into a sheet and then created your Pivot Table from that Structured Table.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

Ok, I put a humongous number into Amount.

In the PT, right-click the Amount field and select Value Field Settings and then select Number Format and Currency.

Currency will give you the precision you need.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[raven4]

Thank you so much, SkipVought! The field Number Format in the Pivot Table did the trick.

Thanks for helping out this pivot table beginner!

 

[raven4]

Yes, I did that already.