Excel Pivot Table Blank Category Not Displaying Pr

Question

I have two pivot tables:

Pivot table one's data source is an Access export to Excel which involves manually copying and pasting the data from the export file into the report file data table which feeds data to the pivot table.

Pivot table two's data source is a workbook connection, to the same query that is used to create the old export file, which feeds date to the new report file's data table which is then used by the pivot table.

My problem is that the pivot table that is created from data that is derived from the workbook connection displays blanks as an actual blank " ", not "(blank)" which is found in the original table.

Screenshot 1 - Normal Blank

Screenshot 2 - Weird Blank

The strangest part is that when I go to the source table in Excel, where the data connection actually places the data in the workbook, blanks show up as "(blank)" when I go to filter the data in the table.

Screenshot 3 - Weird Blank Source Table

I could not find anything like this online. Any help will be greatly appreciated!

Answer 1

Excel tends to add invisible formatting characters, that (usually) don't get imported when importing with a command, but do get copy-pasted. These need to be removed.

Paste the following function into a module:

Public Function RemoveNonASCII(str As String) As String
    Dim i As Integer
    For i = 1 To Len(str)
        'Append the question marks
        If Mid(str, i, 1) = "?" Then
            RemoveNonASCII = RemoveNonASCII & "?"
        End If
        'Append anything that isn't a questionmark
        If Asc(Mid(str, i, 1)) <> 63 Then
            RemoveNonASCII = RemoveNonASCII & Chr(Asc(Mid(str, i, 1)))
        End If
    Next i
End Function

Then, execute the following query to remove all excess spaces and formatting characters in Access:

UPDATE MyTable SET MyTable.MyColumn = Trim(RemoveNonASCII(MyTable.Mycolumn))