How to exclude specific data through Formula

[Mayank09]

Hello All,

I am working on Power BI Dashboard and struck at one point. I have huge size data with multiple fields. In one of the tables, I am trying to show the Order count. The problem I am struggling, there are certain orders that start from 1,2,3 (like 101,212,320) and the length is more than 7 . I need to exclude those only. Please help me with how I can exclude it.

Some other point:

Normal Order number Max length - 7

Order Starts from 1 (101,112,124) length more than 7

Thanks,

[Gabriel_Walkman]

Hi!

there are certain orders that start from 1,2,3 (like 101,212,320)

---

What is an order in this case? Do you mean an order id? Is your example a list of three numbers, or one long number that you just added commas to? Is your example in text format or number format?

Try making a custom column with Text.Length( Text.From( [orderColumn] )), then just filter that column to be <=7.

[Mayank09]

@Gabriel_Walkman Thank you for your reply. Please see the attached example for more details.

This is the sample list of all order number types. I just wanted to exclude a long type of Order number from the list (see the top 6-7 orders starts from 101, 103, 121, 215, 315). Please let me know how I create any new measure for an order number or any modification to the existing field.

order Numbers

10152020002

10152020003

10152020038

11152020001

12152020007

12152020013

2152020227

2152020297

3152020272

3152020272

5541831

5541831

5541831

20895

20895

20895

4503160

4503160

5541830

7502350

7502350

CON2007-65328

CON2007-65328

CON2007-65328

Q-44852

Q-44852

Q-44852

[Gabriel_Walkman]

"Please let me know how I create any new measure for an order number or any modification to the existing field."

I'm sorry, but this is extremely basic stuff, so I'll have to recommend googling. I'd do this in power query by creating a new custom column.

[Mayank09]

@Gabriel_Walkman I know how to create a measure.

If you know the formula - how to exclude those order numbers, let me know.

[Gabriel_Walkman]

What should happen to CON2007-65328 or Q-44852? The first is >7 characters long.

[Mayank09]

@Gabriel_Walkman Basically, In my data there is two types of orders :

1. Long Order number 10152020002, 2152020227, etc. (Offline added Orders)

2.  CON2007-65328, Q-44852, 7502350, 20895, 5541831, etc. (system generated Orders)

So i just need to delete these Offline added orders only and keep all system-generated order in my data 

Thanks,

[Mayank09 · Accepted solution]

@Gabriel_Walkman it solved. I have created the formula. 

[v-yingjl]

Hi @Mayank09 ,

Glad to hear the issue is solved. You can kindly share your solution and accept it as solution, that way, other community members could easily find the answer when they get same issues.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Gabriel_Walkman]

Great! Please mark your own post as a solution.