Power Query Prefix Based on Another Column

[left4pie2]

My goal for this column is to split the two values up and subtract them. The problem is many of the values to the right of the "/" don't include the first two numbers. So for example 1945/60 should be abs(1945-1960) = 15

I split these two columns up using the split column but now I need to use some logic to say if Face1.2 has < 4 characters add a prefix to Face1.2 from the left two characters of Face1.1

I tried to create the prefix with this line but I got an error that it didn't recognize the LEFT() function.

Thanks for the help!

[v-yanjiang-msft · Accepted solution]

Hi @left4pie2 ，

According to your description, I have two methods.

Method1--In Power Query

1. Split Column by Delimiter.

2. Split column Face1.1 by Position.

3. Add a custom column.

=if Text.Length([Face1.2])<4 then[Face1.1.1]&""&[Face1.2]else[Face1.2]

4. Merge Face1.1.1 and Face1.1.2 columns, get the expected result.

Method2--In DAX

1. In Power Query, split column by delimiter.

2. Change the data type of Face1.2 to Text.

2. Create a calculated column in DAX.

Column =
IF (
    LEN ( 'Table'[Face1.2] ) < 4,
    CONCATENATE ( LEFT ( 'Table'[Face1.1], 2 ), 'Table'[Face1.2] ),
    'Table'[Face1.2]
)

Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[left4pie2]

This worked! Thank you!