Inserting a dash based on certain criteria in Power BI

[mkwiatkowski]

Hello,

Hope someone can help with this.

I've got a column in Power BI that has aircraft registrations but they are without the required dash separator as shown the screenshot

What I would like to be able to do is create another column and have some way of adding the dash in but based on certain criteria depending on the registration. E.g.

If Regno starts with OOxxx then OO-xxx

If Regno starts with Dxxxx then D-xxxx

If Regno start with YUxxx then YU-xxx

and so on..

Tried conditional columns but that doesn't work. Was thinking along the lines of a custom column that inserts a dash but this inserts the dash at an arbutry position and not depending on the type of registration condition:

= Table.AddColumn(#"Changed Type1", "Custom2", each Text.Insert(Text.From([REGNO]),2,"-"))

Any thoughts or suggestions would be much appreciated

[V-lianl-msft · Accepted solution]

Hi @mkwiatkowski ,

You can try to create a new column like below:

Column = 
var left_1 = LEFT('Table'[Regno],1)
var left_2 = LEFT('Table'[Regno],2)
VAR right_1 = SUBSTITUTE('Table'[Regno],left_1,"")
var right_2 = SUBSTITUTE('Table'[Regno],left_2,"")
return SWITCH(TRUE(),
                left_1 IN {"D","L"},COMBINEVALUES ( "-", left_1, right_1 ),
                left_2 IN {"OO","YU"},COMBINEVALUES ( "-", left_2, right_2 ))

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.