Power Query Prefix Based on Another Column

Question

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 Answer

Hi&nbsp;left4pie2&nbsp;，
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])&lt;4 then[Face1.1.1]&amp;""&amp;[Face1.2]else[Face1.2]
4. Merge Face1.1.1 and Face1.1.2 columns, get the expected result.

&nbsp;
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] ) &lt; 4,
    CONCATENATE ( LEFT ( 'Table'[Face1.1], 2 ), 'Table'[Face1.2] ),
    'Table'[Face1.2]
)

Get the expected result.

&nbsp;
I attach my sample below for reference.
&nbsp;
Best Regards,Community Support Team _ kalyj
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.
&nbsp;
&nbsp;

Forum Discussion

Power Query Prefix Based on Another Column