Add space in text field

[AOD]

Hi All,

 My requirement is to add space in  the text field on one of the column of table.

Eg: I have postcode as text field  as B30123 , B9234

I want new column to be created which will check length of the field value and add space

Eg B30123 - Length is 6 so i want new value to be B30 123

B9234 - Length 5 -> B9 234

Thanks for your

[BA_Pete · Accepted solution]

Hi @AOD ,

In Power Query, you can add a new custom column and write code something like this:

=
if Text.Length([postcode]) = 6 
then Text.Combine({Text.Start([postcode], 3), Text.End([postcode], 3)}, " ")
else if Text.Length([postcode]) = 5 
then Text.Combine({Text.Start([postcode], 2), Text.End([postcode], 3)}, " ")
else "Another pattern")

This gives me the following output:

The principle would be the same in DAX, except you would use LEFT() instead of Text.Start(), RIGHT() instead of Text.End, and '&' instead of Text.Combine.

Here is @nvprasad 's DAX solution converted to a Power Query column formula, if you want to push your transformations away from the data model (you should!):

= Text.Combine({Text.Start([postcode], (Text.Length([postcode]) -3)), Text.End([postcode], 3)}, " "))

Pete

[nvprasad]

HI 

Yo can get by below code.

New Column = LEFT('Sample'[Code],LEN('Sample'[Code])-3)&" "& RIGHT('Sample'[Code],3)

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad