Help with M code conditional column

[Marija_JC]

Hello, 

I woluld like to create an coditional column based on the text values from another column. So, it should be like:

if column B has value " x", then text in column A sholud start from 4th character, else

if column B has valu"y", then text  in column A should be extracted befor delimiter, else

if column B has value"z", then in column A should be in the middle of certain delimiters.

How should M code look like in Custom column?

Thanks in advance

[v-agajavelly]

Hi @Marija_JC ,

I hope the response provided helped in resolving the issue. If you still have any questions, please let us know we are happy to address.

Regards,
Akhil.

[v-agajavelly]

Hi @Marija_JC ,

Just wanted to check back were you able to validate the solution for the "X", "Y", and "Z" conditions? If any scenarios are still giving unexpected results, I can walk you through some alternate logic or help troubleshoot further. Let me know.

Regards,
Akhil.

[v-agajavelly]

Hi @Marija_JC ,

Did you get a chance to try the solution above? Just checking in to see if it worked as expected for your "X", "Y", and "Z" conditions, or if you’ve run into any edge cases where it behaves differently.

Regards,
Akhil.

[v-agajavelly]

Hi @Marija_JC ,

Thanks @MarkLaf  for the clean and accurate solution  your logic perfectly aligns with the expected output shared by OP. 

Hey @Marija_JC  did you get a chance to look at the solution above? It seems to cover all your conditions for "X", "Y" and "Z"exactly as described and shown in your screenshot. Let us know if it works as expected on your end or if you ran into any edge cases happy to help further.

Regards,
Akhil.

[Omid_Motamedise · Accepted solution]

use the following formula

= 
if [ColumnB] = "x" then
    Text.Middle([ColumnA], 3) 
else if [ColumnB] = "y" then
    Text.BeforeDelimiter([ColumnA], "-") 
else if [ColumnB] = "z" then
    Text.BetweenDelimiters([ColumnA], "(", ")") 
else
    null

[burakkaragoz]

Hi @Marija_JC ,

The solution( @MarkLaf ) looks solid, but I'd tweak a couple things to make it more bulletproof:

if [Column B] = "x" then 
    if Text.Length([Column A]) > 3 then Text.RemoveRange([Column A], 0, 3) else [Column A]
else if [Column B] = "y" then 
    Text.BeforeDelimiter([Column A], "_")  // assuming underscore based on your sample
else if [Column B] = "z" then 
    Text.BetweenDelimiters([Column A], "_", "-")
else 
    [Column A]  // keep original instead of null

Main improvements:

• Length check for "x" case (what if your text is only 2 characters?)
• Return original text instead of null for unmatched cases
• Delimiter choice for "y" - space vs underscore depends on your actual data

The original answer works fine if your data is clean, but real-world data usually has edge cases that break simple logic.

What delimiters are you actually working with? That'll determine whether you need space or underscore for the "y" condition.

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

[MarkLaf]

Given a table like below:

Column A	Column B
text1 text2_text3-text4	x
text1 text2_text3-text4	y
text1 text2_text3-text4	z
text5 text6_text7-text8	x
text5 text6_text7-text8	y
text5 text6_text7-text8	z

I think the following code in a custom column would get what you want:

if [Column B] = "x" 
    then Text.RemoveRange( [Column A], 0, 3 ) else 
if [Column B] = "y" 
    then Text.BeforeDelimiter( [Column A], " " ) else
if [Column B] = "z" 
    then Text.BetweenDelimiters([Column A], "_", "-") else
null

Output: