Newb: Using Text filters on a huge data set is slow. Any alternatives?

[Anonymous]

I am a new PowerBI user. 

I am trying to filter a ADO Bug data set using the texts in the Title(text) column. I am trying to use "Text query" but it is taking forever even for one condition.

I am sure there must be another way besides just right click and using Text queries. I am stalled at this point and would appreciate any help in this regard.

Thanks

[amitchandak]

@Anonymous ,Can you please explain with an example.

refer if this can help https://exceleratorbi.com.au/text-filtering-power-bi/

[Anonymous]

Sure @amitchandak 

I need to filter and remove anything and everything that has a particular text. For example, in the attached I need to remove or filter out anything that has "Technical" in the title

Like wise I have many other texts

[vanessafvg]

if you have different options as delimters ie technical has a - after it, and td has a : after it, i would do replace on one of those with just one type and then split the column in power query using that delimiter, so change all the td: to td- etc.. and then use - to split the column.

[vanessafvg]

can you give an example of your data and what exactly you are doing?

[Anonymous]

This is just a very small sample

I need to remove anything that has TD, TeCh, Techincal, Techical Defect, Splunk error etc

I also looked at the M Code( if I think it is) thrugh Advance Query editor 

[vanessafvg]

is the type of error always in the beginning of the value  with a :  if so you can duplicate the column and split the first value off by the delimiter : and then filter those?

[Anonymous]

I thought of and tried that too but the text may occur at any position in the string. That is what is making it challenging other than the slowness

[vanessafvg]

so what you say is that each line of text has the error message at any time not at the beginning of the string?

[Anonymous]

That is correct @vanessafvg 

[vanessafvg]

so what you saying is each line of text has the error message at any point not in the beginning of the string?

[Anonymous]

Yes, that is correct @vanessafvg 

[Greg_Deckler]

I would definitely use M code for this I think if you never want to see those rows. I would create a new column with something like:

=Text.PositionOf([Error Column], “TD”) + 1

You then only keep the zeros.

@Anonymous 

[Anonymous]

can I use this code if I have mulitpleslike TD, Tech, Technical etc

 as

=Text.PositionOf([Error Column], “TD”,"Tech") + 1

@Greg_Deckler 

[Greg_Deckler]

@Anonymous - No, you would do it this way:

=Text.PositionOf([Error Column],"TD") + 1 + Text.PositionOf([Error Column],"Tech") + 1 + Text.PositionOf([Error Column], "Something else") + 1

If no matches, will return 0, keep only the zeros.

[Anonymous]

I tried this 

let
Source = VSTS.AnalyticsViews("GEICO", "Billing", null),
#"Private Views_Folder" = Source{[Id="Private Views",Kind="Folder"]}[Data],
#"0b13fece-ab92-4754-b484-41ecef303333_Table" = #"Private Views_Folder"{[Id="0b13fece-ab92-4754-b484-41ecef303333",Kind="Table"]}[Data],
#"Removed Duplicates" = Table.Distinct(#"0b13fece-ab92-4754-b484-41ecef303333_Table", {"Work Item Id"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([Originating Environment] = "Extended Development" or [Originating Environment] = "Extended Integration" or [Originating Environment] = "Extended User" or [Originating Environment] = "Integration" or [Originating Environment] = "Production" or [Originating Environment] = "User")),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Created Date", type date}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Bug Root Cause] = "Change in Requirements" or [Bug Root Cause] = "Code Issue" or [Bug Root Cause] = "Code Issue - Unexpected Results" or [Bug Root Cause] = "Code Regressed")),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each not Text.StartsWith([Title], "TD") or not Text.Contains([Title], "TD") or not Text.StartsWith([Title], "Data Fix") or not Text.StartsWith([Title], "Data fix") or not Text.Contains([Title], "Datafix") or not Text.StartsWith([Title], "TD") or not Text.StartsWith([Title], "Tech") or not Text.Contains([Title], "Technical") or not Text.StartsWith([Title], "Splunk") or not Text.Contains([Title], "splunk") and not Text.Contains([Title], "LogCop"))
in
#"Filtered Rows2"