Add space in text field

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