PBI ignores / trims empty spaces at the end of the value in Report/Data/Model view.

[dariuszw]

PBI ignores / trims empty spaces at the end of the value in Report/Data/Model view, but still recognizes empty spaces in the Power Query view. As a result, if column is being used as primary key, the column can not be used to link with other table giving an error of unsupported relationship because of not having unique values in one of the columns. Which is not true. This issue concerns incorrect aggregation as well.

Example:
I have created 2 tables (Table A and Table B) with 2 columns and 2 rows each, where one the columns is ID and first row ID has no spaces at the end, while second row ID has 10 spaces at the end.

After coping ID column from PowerQuery view to Notepad++ we can see that spaces are visible

But if we switch to Report/Data/Model View and copy-paste ID column again to Notepad++ the spaces are gone.

So if we want to link Table A and Table B using ID column we get error message as below

I attach queries for both tables:

Table A:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nSJN1TSUTJUitWBcBTgAChspBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Value", Int64.Type}})
in
    #"Changed Type"

Table B:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nSJN1TSUXJJLU5WMFSK1YGIKMABTM5IKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Description", type text}})
in
    #"Changed Type"

[v-yanjiang-msft]

Hi @dariuszw ,

By my tests, we may not keep the trailing spaces when loading the data. It will only load the space before the character or the space between the characters.

As far as I know, when strings are imported into Analysis Service Engine, the trailing spaces are trimmed. So the disappearing of the trailing spaces is by design. Here I suggest you to replace the trailing spaces by other characters like "_".

You could also submit your idea in Power BI ideas Forum to improve this feature.

Best regards,

Community Support Team_yanjiang

[dariuszw]

Hi,

if trailing spaces are trimmed by design then it is very wrong. It's a data manipulation without user's knowledge or permission. Yes, I could replace the trailing spaces by other characters but this means that every user should do it by deafult for each and every column just in case there are trailing spaces. Sorry but it makes no sense to me. I don't think it should be raised as an improvement, for me it looks like bug.