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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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