SQL Query on Import Not Working

[BekahLoSurdo]

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):

SELECT
	ITEM.ITEM_ID Item,
	TRIM(ITEM.ITEM_ID) TrimmedItem
FROM DELTEK.ITEM ITEM

Results (after some PQ columns and filtered by rows with trailing spaces):

 

As you can see, the last 4 rows are trimmed correctly but the first 3 are not, despite them all having character 32 as their last character. 

Unfortunately, I can not duplicate this with sample data as if you use '500P55W565LJ3H ' explicitely, it works correctly. So it is something about the record itself coming from the DB. I'm hoping that someone has seen something similiar since we can't duplicate this. 

I have also tried RTRIM(), TRIM(' ' FROM ITEM_ID), TRIM(CHAR(32) FROM ITEM_ID)... nothing seems to work. TIA

[v-alq-msft]

Hi, @BekahLoSurdo 

I'd like to suggest you use Text.Trim()  function in 'Query Editor' to remove all leading and trailing whitespace from the specific text. I created sample data to reproduce your scenario. You may also use Text.Length() to check if a text contains leading and trailing whitespaces.

Table:

Here is the codes in 'Advanced Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUkhMSlZQUIrViVZKSU2DstIzMkGsWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}),
    Custom1 = Table.AddColumn(#"Changed Type","Length before trim",each Text.Length([Text])),
    Custom2 = Table.AddColumn(Custom1,"TrimText",each Text.Trim([Text])),
    Custom3 = Table.AddColumn(Custom2,"Length after trim",each Text.Length([TrimText]))
in
    Custom3

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BekahLoSurdo]

Thanks @v-alq-msft but I have a few more grouping / ranking steps I need to do in SQL before importing into PBI and having that option available. Not trimming them before ranking was incorrectly counting non-trimmed items as unique values and giving them distinct ranks. 

[az38]

@BekahLoSurdo 

also have a look here in order to fix the issue in SQL-query https://stackoverflow.com/questions/27915149/sql-server-rtrimltrimcity-not-stripping-whitespace

[az38]

Hi @BekahLoSurdo 

it looks like you have other special characters in trimmed cells

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

[BekahLoSurdo · Accepted solution]

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!