Hi Vijay,

Looks like there are some non-printing characters in the cells of the FX table that appear blank. If you select a bunch of empty cells, you'll see there is a count in the status bar. If the cells were empty you wouldn't see this. 

That said, I was able to insert a PivotTable (see attached), but the PivotTable defaults to counting the cells that contain numbers instead of summing them. Another sign that there's text or non-printing characters in those cells.

I'd load the table to Power Query, make sure all empty cells display as 'null'. If not, use the data cleaning tools to replace any text/non-printing characters with null and then load to your PivotTable.

Mynda

Hallo Mynda. Thanx for addressing the issue. Do you mean that some of the entered numbers are in text format?

You are correct that there are some non-printing characters in the cells which are apparently empty as is reflected by the count in the status bar. I have seen this first time and don't know anything about what are they? How can I find all of them to clear them and make those cells empty? Is the 'count in the status bar' is the only way to know about their existence? 

No, I mean the empty cells have non-printing characters. The numbers look ok.

Load the table to Power Query, make sure all empty cells display as 'null'. If not, use the data cleaning tools to replace any text/non-printing characters with null and then load to your PivotTable.

Mynda

Power query will permanently reside in the file resulting into duplicate data being inserted into the file only for the purpose of cleaning primordial non-printing characters which possibly came from some formula operations on old data and which unlikely be popping up in the future with new data entry. Besides if refreshing the query does not automatically refreshes pivot table (For which power query will be the source of data) then each time I will have to refresh both query and pivot table which is a kind of a hassle. 

I will instead try to get rid of the characters using find and replace by copying them into find field. If this doesn't works then as a last recourse I will have to resort to manual deletion. Nevertheless, pinpointing the source of problem was very helpful and I thank you again for that. 

But why don't you use PQ to do this one-time cleaning for you. It takes about 30 seconds to connect to FX, select all columns and execute the Clean and Trim functions (on the Transform tab, under the Format button). Finally, set all data types correctly and load toa table.

Then copy that PQ generated table as values on top of the original FX table and delete the query and it's output. Now you'll have a clean FX table that can be the basis for your pivot table.

You seems to be right that I can copy paste the values from the query and then delete it. Can you tell me the exact steps after the table is loaded to query; to delete the non-printing characters without causing any harm such as alteration or deletion to my other entered numbers? 

The attached file contains the PQ clean-up query and the resulting table loaded to the Query sheet. You'll note that this table has the same dimensions as the one in the RULES sheet. Same numbers in the same cells, just with not invisible characters. All empty cell are now real nulls, just as Mynda explained.

Copy B2:W63 from sheet Query. Paste as values into B2 on sheet RULES.

Now you can delete the Query sheet and delete the query from the Queries & Connections sidebar.

Note that the attached worbook has two queries. After I did the first one I played around a bit to see if I could come up with a better solution. But I gave up on that and forgot to delete the query.

Yes Riny it works without harming integrity of the entered data. It's cool. You did very well. Thank you very much. I appreciate your help.