Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
sustar2020
Regular Visitor

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

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.

 

sustar2020_0-1651677006446.png

 

Thank You.

 

1 ACCEPTED SOLUTION
KT_Bsmart2gethe
Impactful Individual
Impactful Individual

Hi @sustar2020 ,

 

Add a custom column:

KT_Bsmart2gethe_3-1651681192890.png

 

KT_Bsmart2gethe_1-1651681019756.png

 

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")

 

View solution in original post

6 REPLIES 6
KT_Bsmart2gethe
Impactful Individual
Impactful Individual

Hi @sustar2020 ,

 

Add a custom column:

KT_Bsmart2gethe_3-1651681192890.png

 

KT_Bsmart2gethe_1-1651681019756.png

 

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
Regular Visitor

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
Regular Visitor

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.

 

This works

KDM_HOSS_0-1651679299688.png

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

That would help troubleshoot your issue.

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:

sustar2020_0-1651681024935.png

Thank you very much for your help.

 

Cheers

Greg_Deckler
Super User
Super User

@sustar2020 Add a conditional column:

Greg_Deckler_0-1651677872043.png

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors