Forum Discussion
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):
SELECT
ITEM.ITEM_ID Item,
TRIM(ITEM.ITEM_ID) TrimmedItem
FROM DELTEK.ITEM ITEMResults (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
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!
5 Replies
Hi BekahLoSurdo
it looks like you have other special characters in trimmed cells
to debug, check LEN([Item]) and LEN([TRIM(Item))
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!
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
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 Custom3Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
