Forum Discussion

BekahLoSurdo's avatar
BekahLoSurdo
Resolver IV
6 years ago
Solved

SQL Query on Import Not Working

Has anyone had any issues with SQL functions (during the import process) acting inconsistently? Specifically, the TRIM() function is operating on some rows and not others. Import SQL code (simplified...
Need Help
  • BekahLoSurdo's avatar
    BekahLoSurdo
    6 years ago

    Thanks az38. I checked the length as part of my troubleshooting and had moved on to checking the actual ASCII value of the last character as I knew the length was different. Giving you a kudos though because someone else may have the same question.

     

    I ended up checking the ASCII characters in the SQL query instead of PQ and it turns out the ones that didn't get trimmed automatically actually had a non-breaking space (character 160) instead of a space --- but then when imported into PBI, this was translated back to character 32 - cool, right? I fixed it with this SQL code:

    SELECT
	RTRIM(ITEM.ITEM_ID, CHR(32)||CHR(160)) Item
...

    Thanks for your help!

az38's avatar
az38
Community Champion
6 years ago

Hi BekahLoSurdo 

it looks like you have other special characters in trimmed cells

to debug, check LEN([Item]) and LEN([TRIM(Item))

 

BekahLoSurdo's avatar
BekahLoSurdo
Resolver IV
6 years ago

Thanks az38. I checked the length as part of my troubleshooting and had moved on to checking the actual ASCII value of the last character as I knew the length was different. Giving you a kudos though because someone else may have the same question.

 

I ended up checking the ASCII characters in the SQL query instead of PQ and it turns out the ones that didn't get trimmed automatically actually had a non-breaking space (character 160) instead of a space --- but then when imported into PBI, this was translated back to character 32 - cool, right? I fixed it with this SQL code:

SELECT
	RTRIM(ITEM.ITEM_ID, CHR(32)||CHR(160)) Item
...

Thanks for your help!