cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kinnaree Frequent Visitor
Frequent Visitor

Removing few characters from rows

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Vvelarde Super Contributor
Super Contributor

Re: Removing few characters from rows

hi @kinnaree

 

You can do this with a Conditional Column in Query Editor

 

AddConditionalColumn.png

 

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

 

you can modify the M Code to:

 

IgnoreCaseSensitive.png




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

8 REPLIES 8
vanessafvg Super Contributor
Super Contributor

Re: Removing few characters from rows

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

 

Capture.PNGdd

 


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Super User
Super User

Re: Removing few characters from rows

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?

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Vvelarde Super Contributor
Super Contributor

Re: Removing few characters from rows

hi @kinnaree

 

You can do this with a Conditional Column in Query Editor

 

AddConditionalColumn.png

 

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

 

you can modify the M Code to:

 

IgnoreCaseSensitive.png




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Highlighted
kinnaree Frequent Visitor
Frequent Visitor

Re: Removing few characters from rows

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 Super Contributor
Super Contributor

Re: Removing few characters from rows

@kinnaree

 

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




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




kinnaree Frequent Visitor
Frequent Visitor

Re: Removing few characters from rows

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

 

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 Super Contributor
Super Contributor

Re: Removing few characters from rows

@kinnaree

 

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

 

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




kinnaree Frequent Visitor
Frequent Visitor

Re: Removing few characters from rows

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 286 members 3,091 guests
Please welcome our newest community members: