How to search a substring with **D** in the Column "Vendor Name" - M Query

[sustar2020]

Hi There,

I want to add a new conditional column in Power Query with the name "Status". For all the vendors that have **D** in their name should have the status delisted and the rest will have the status live. I am not able to search based on **D** as it's returning all the values. Please advise.

Thank You.

[KT_Bsmart2gethe · Accepted solution]

Hi @sustar2020 ,

Add a custom column:

Code for the step:

Replace blue text with the previous step.

1. Table.AddColumn(Data0, "Status", each if Text.Contains([Column1],"**D**") then "Delisted" else "Live")

if for some reason 1 does not work, then add Text.From(). See 2

2.Table.AddColumn(Data0, "Status", each if Text.Contains(Text.From([Column1]),"**D**") then "Delisted" else "Live")

[sustar2020]

I want the desired output as:

Vendor Name                         Status

ABC**D** ltd (GB)	Delisted
ABC Food Ltd (GB)	Live
Thames Valley **D** (GB)	Delisted
Wild Harvest Ltd (GB)	Live
Wild Harvesters Ltd**D** (GB)	Delisted

[sustar2020]

Hi Greg,

Thank you for the reply.

I tried this already. Its not working. It is showing all the vendors as 'live' as it's taking '*' as a wild card search and not as a string literal.

[KDM_HOSS]

This works

If this doesn't work can you share a screenshot of the code and table?

That would help troubleshoot your issue.

[sustar2020]

Hi There,

"**D**" didn't work in a conditional column like the way you said. However when I looked to check the postion of "**D**" in a Custom Column it worked. Not sure why?

Here's the code below that I have used:

Thank you very much for your help.

Cheers

[Greg_Deckler]

@sustar2020 Add a conditional column: