Forum Discussion
Help with M code conditional column
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
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
7 Replies
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 nullGiven 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 nullOutput:
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 nullMain 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.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.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.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.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.
