Leading and Trailing Spaces

[Anonymous]

Hi,

I feel like Power BI is automatically trimming my data. Is there a way to not have that happen?

I am importing txt files with | as the delimeter. Lets say "21B", "21B " and " 21B" are the data values for one of the fields, Power BI will automatically see them as "21B" without the leading and trailing spaces which is not what I want. I need to detect the spaces in my work process. 

I don't see any options to have this undone (transform -> format, only has trim which its already doing for unknown reason), and I haven't applied any steps to trim it. I also don't see any helpful option in the data source setting. 

Please assist.

 

[HotChilli]

My experience is as-per this article

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-data-types#leading-and-trailing-spac... 

powerbi engine removes trailing spaces but not leading spaces. This is not Power Query (which leaves them in).

[Anonymous]

This is what i have in the 'transform data' section, as you can see i did not trim anything. 

Here is my txt file:

As you can see there are about 16 cases where there are spaces in them, thus the length is >3.

but with this code:  "RouteIDError = IF(NOT(LEN('Duplicate'[Route ID]) = 3), "Route ID ", "")", I only get 5 RouteIDError, it does not count the trailing spaces. 

How do I have powerbi include the leading and trailing spaces? Please assist.

[PwerQueryKees]

That is not your M code. Open the advanced editor, found in the view menu and copy paste.

The problem may be in the change type step. If it changes to numeric, it will definitely remove spaces....

[Anonymous]

 This is what i have

[PwerQueryKees]

Can you share your M code? All steps from loading the file to where your query fails?

[Anonymous]

This is what i have in the 'transform data' section, as you can see i did not trim anything. 

Here is my txt file:

As you can see there are about 16 cases where there are spaces in them, thus the length is >3.

but with this code:  "RouteIDError = IF(NOT(LEN('Duplicate'[Route ID]) = 3), "Route ID ", "")", I only get 5 RouteIDError, it does not count the trailing spaces. 

How do I have powerbi include the leading and trailing spaces? Please assist.

[HotChilli]

"I feel like Power BI is automatically trimming my data" - Where are you seeing that happening? Can you show us please?

[Anonymous]

So I have this "RouteIDError = IF(NOT(LEN('Duplicate'[Route ID]) = 3), "Route ID ", "")", which checks that Route ID (text format), and if it is not a len of 3, it would return "Route ID ", otherwise blank. But it does not count leading and trailing spaces. Such that " 21B" and "21B ", should be counted as a len of 4 including the space but it does not. But if there is a space in between the text, like "21 B" or "2 1B", it would count it as len of 4. So it does automatically trims it and ignores the leading and trailing spaces. So I'd assume it's an issue with the data source settings, but I can't find any option that deals with this.