Removing few characters from rows

[Anonymous]

I have a column that contains versions of phones. It contains the following rows:

ios 8.3 (0.03K)

IOS 9.3.1 (1.49 k)

IOS 10.1

Android 5.0.1

Android 1.4.2 (OK)

IOS 9.3.2 (OK)

I have about 1 Million rows of data. I want to remove everything except IOS  or Android. Is there a way i can achieve it?

[Anonymous]

Thank you so much for the help everyone. But in a column that has both Android and IOS following the versions. I just want to create a column that specifies, whether it is an Android or an IOS operating system.

So my results should something like this:

IOS

IOS

IOS

Android

Android

IOS

Instead of:

ios 8.3 (0.03K)

IOS 9.3.1 (1.49 k)

IOS 10.1

Android 5.0.1

Android 1.4.2 (OK)

IOS 9.3.2 (OK)

Is there a DAX query for that, or any other way to do it?

[Vvelarde]

@Anonymous

Hello, To understand your Question, What is the difference between my answer and the result expected?.

[Anonymous]

Hello, I tried to do what you suggested and succeeded. Thank you so much for your help. Really appreciate it. 

I just wanted to understand, what if there were Blank values in the column and I want to include that along with Android and IOS. Is there a way to do that too?

[Vvelarde]

@Anonymous

You can add a new condition in the Conditional Column to IOS. ---> IOS And ELSE Condition  leave blank or enter "Other OS".

[Anonymous]

Thank you so much for sharing the information. I really appreciate it

[Vvelarde · Accepted solution]

hi @Anonymous

You can do this with a Conditional Column in Query Editor

And if you have problem with Android and ANDROID (Case sensitive) (Following @ImkeF suggestion)

you can modify the M Code to:

[ImkeF]

In the query-editor, add a step to your table:

Filter = Table.SelectRows(<NameOfThePreviousStep>, each Text.Contains([MyColumn], "IOS", Comparer.OrdinalIgnoreCase) or Text.Contains([MyColumn], "Android", Comparer.OrdinalIgnoreCase))

This is hand-edited code from the standard-filter from the table-header, as you probably want a case-insensitive match here?

[vanessafvg]

you can filter in the query editor

ie.  drop down the column you want with the arrow key like ive done on product and click on text filters and select contains

dd