Log In
Register
Home
Active Member
Excel Expert
November 19, 2022
Offline
I'm referencing an existing table in a separate workbook as my source. PQ is displaying 0 instead of null for multiple source fields when XLOOKUP returns null ("") for the "if not found" argument. The source worksheet properly displays a null value, but PQ shows it as a zero.
PQ defaults the format to whole number (123) even though there are text entries in the list (XLOOKUP true results). This is presumably because all text entries begin with a 3 digit number (such as 100 West). Changing the field to Text doesn't remove the zeroes. It just moves them to the left. 🙂 
How do I correct this? I have attached a screenshot to illustrate.
Thank you for any help,
Don
Trusted Members
Moderators
November 1, 2018
Offline
PQ should not return 0 for a cell that contains "" (which is not the same as null, by the way). Are you sure the source sheet doesn't simply have the display of 0 suppressed, either for the sheet or for that column?
Active Member
Excel Expert
November 19, 2022
Offline
Hi Velouria,
Thank you for replying. Yes, I'm absolutely sure - nothing is suppressed. The screenshot was designed to show the formula illustrating the use of "".
Trusted Members
Moderators
November 1, 2018
Offline
Yes, but a screenshot doesn't really help if the display is suppressed. 😉 If you alter the XLOOKUP to remove the 'if not found' argument do all the rows in your screenshot then show errors?
Active Member
Excel Expert
November 19, 2022
Offline
Removing 'if not found' argument does not show errors. So I looked in the XLOOKUP source table and confirmed there is no value in the cell (no data, no formula). If I enter 1, it shows 1 in PQ. If I enter ="" PQ shows null. Note the source table uses PQ to import from a CSV. When I review the PQ import, the columns in question show null as shown in the screenshot here: 
Trusted Members
October 18, 2018
Offline
Pictures are of little value as we can not ascertain the format or functionality of the cell values. Suggest you upload a sample file that is representative of your actual file so that we can analyze it properly. Additionally, data in pictures cannot be manipulated to ensure that our solutions are fully tested before providing it to you.
Trusted Members
Moderators
November 1, 2018
Offline
If your XLOOKUP is finding a match but the cell in the result column is blank, it will return 0. If you can't see the 0, then either the cell format or your excel options are set to suppress the display of 0 values.
1 Guest(s)
