cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
left4pie2
Helper I
Helper I

Add prefix to column based on another column

left4pie2_1-1646165516951.png

I'm trying to break this data up into two columns that I can subtract from each other. So for example I'd like to change 1950/ 50 to 1950 - 1950 = 0

The problem is sometimes the number on the right of the / only has two digets. So for example 1940/60 should be 1940/1960. 

 

left4pie2_3-1646165548742.png

left4pie2_2-1646165530710.png

I first seperated the number into two columns based on the / using split column button.

 

Next I'd like to create some logic that says if there are less than 4 characters in Face1.2, append the left two characters from Face1.1 to Face1.2. I'm having trouble doing this. I tried using the prefix option to do this and changed the face to LEFT([Face1.1],2) but I got an error.

left4pie2_4-1646165896566.png

 

Thanks for the help!

 

1 ACCEPTED SOLUTION
Daryl-Lynch-Bzy
Super User
Super User

HIi @left4pie2 , the following can be used in the Face.1.2 column in the transformcolumns function.

 

Table.TransformColumns(#"Previous Step", [Face.1.2] , each if Text.Length(_) = 2 then "19" & _ else _ )

 In the next step, you can convert both text columns to Numbers.

 

 

View solution in original post

2 REPLIES 2
Daryl-Lynch-Bzy
Super User
Super User

HIi @left4pie2 , the following can be used in the Face.1.2 column in the transformcolumns function.

 

Table.TransformColumns(#"Previous Step", [Face.1.2] , each if Text.Length(_) = 2 then "19" & _ else _ )

 In the next step, you can convert both text columns to Numbers.

 

 

That worked, thanks!

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors