Shortcut on M's conditional columns (if then else)

[Alex_Ooi]

Hi folks, I'll give  you some background story to my case.

• I am doing financial modelling from my General Ledger
• The descriptions (column title [Description]) is unfortunately not standardized and I have to do a lot of guesswork - obviously this is not something scaleable to clean up data, having to replace text for every single occurence
• So I came up with another column that is supposed to standardize the names (field name [Entity Name])
• The problem is the formula is super long, and I don't know if I could do something sleek about it and make the code block more readable since I am talking about over 150 lines of conditional

the code line that is on repeat has this syntax:

if Text.Contains([Description], "keyword from string", Comparer.OrdinalIgnoreCase) then "Standard Name" else if
...
else null, type text

I read something similar in this link (https://stackoverflow.com/questions/54368921/find-and-replace-using-wildcard-in-power-bi) but I am not sure if I can reproduce the similar results. I am also not familiar about the use of => in M. If anyone here can show and explain to me where and how I could use => in my M script, it would be much appreciated!

[MFelix]

Hi @Alex_Ooi ,

The best option is the M champion @ImkeF .

Can you please check this @ImkeF , 

[ImkeF]

Hi @Alex_Ooi ,

How is the desired behaviour if multiple items match: Do you only want to replace the first match or subsequent as well?

Is it all the same transformation, just with different "if" and "then" strings?

[Alex_Ooi]

@ImkeF thank you so much for reaching out to me!

• Desired behaviour for multiple items match - I am only expecting to replace first match
• It is all the same conditional transformation (if text contains "xyz" then "do this" else ...)

Looking forward to hearing from you soon.

[ImkeF]

Hi @Alex_Ooi 

you can create a "Translation"-table with columns "Old String" (contains the string to search for) and "New String" (the replacement).

Then add a column with this formula:

Table.SelectRows(Translation, (t) => Text.Contains([Column1], t[Old String], Comparer.OrdinalIgnoreCase)) {0}[New String]

It will return the correct values for matches and an error when no match is found.

You can use try .. otherwise to fetch the error or simply replace errors afterward with the desired default-value.

[Alex_Ooi]

I'm sorry @ImkeF I think I wasn't making myself clear. I want to create a column in my current data table. Can I replicate this by using Table.AddColumns?

Also, I think I have misunderstood your question on "multiple matches". I thought you meant multiple matches in the column I am searching for using Text.Contains. Can I know how I could send my script to you? I guess my script would be a better explanation than what I am trying to tell you with words.

Once again, apologies for the confusion.

[ImkeF · Accepted solution]

Hi @Alex_Ooi 

Yes, there could be a misunderstanding, but my solution was expected to work on the original data table. Pls check enclosed file.

If that doesn't work, you can send the file to info at.. thebiccountant.com.