cancel
Showing results for
Did you mean:
Frequent Visitor

## Differentiate between Text & Number and if Text return as Zero

Hello Everyone,

I have a column having results which comprise numbers and text. I am looking to create a new measure which shall work on the logic that if there is a number value in the existing column then just keep it as in the new measure and if there is a text "BDL" in the existing column then mark zero "0" in the new measure.

 Existing Column New Measure Logic Result Result 60 60 If number, keep it as is 70 70 If number, keep it as is 67 67 If number, keep it as is 80 80 If number, keep it as is BDL 0 If BDL, then mark it as Zero 60 60 If number, keep it as is 77 77 If number, keep it as is BDL 0 If BDL, then mark it as Zero 86 86 If number, keep it as is 120 120 If number, keep it as is BDL 0 If BDL, then mark it as Zero 50 50 If number, keep it as is 45 45 If number, keep it as is

Any help is highly appreciable.

Thanks

Samrat

1 ACCEPTED SOLUTION
Resolver I

you can  try this measure

when the current row contains "BDL", CONTAINSSTRING() will return true, then test will return 0, else return current row.

``test = IF(CONTAINSSTRING( MIN('Table'[Existing Column]),"BDL"),0,MIN('Table'[Existing Column]))``

Regards,

OliT

3 REPLIES 3
Community Support

Whether the advice given by @OliT  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Best Regards,
Henry

Resolver I

you can  try this measure

when the current row contains "BDL", CONTAINSSTRING() will return true, then test will return 0, else return current row.

``test = IF(CONTAINSSTRING( MIN('Table'[Existing Column]),"BDL"),0,MIN('Table'[Existing Column]))``

Regards,

OliT

Memorable Member

Hi,

In Power Query add the 'Custom Column' and write the below M query :

if Value.Type([Result]) = type number then [Result] else 0

Mahyartf

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors