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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

 


Follow on LinkedIn
@ 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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors