Log In
Register
Home
January 27, 2023
Offline
I want to insert pivot table in the sheet named 'STATS' from the table named 'FX'. Excel is refusing to insert. Can someone please troubleshoot? I have attached the file.
See Answer
July 16, 2010
Offline
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
January 27, 2023
Offline
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?
July 16, 2010
Offline
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
January 27, 2023
Offline
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.
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
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.
January 27, 2023
Offline
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?
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
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.
Answers Post
1 Guest(s)
