Forum Discussion

BekahLoSurdo's avatar
BekahLoSurdo
Icon for Resolver IV rankResolver 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!

v-alq-msft's avatar
v-alq-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
    BekahLoSurdo
    Icon for Resolver IV rankResolver IV
    6 years ago

    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.