Case insensitive table.findtext across multiple columns

[MStaford]

Hi All,

I have a situation where I have to bring across only those rows where a specific values specific value (lets say, "true")  appears in 5 selected columns with multiple files with each including over 200 to 400K rows.

Minimum rules - The "true" can be atleast in one column to qaulify the extraction of row.

Additionally, the text in the column inconsistent with some files has it uppercase and other have it in lowercase.

Following is the dummy dataset.

id	bla1	yaba2	daba3	lama	doo
1222	true	true	true	true	true
1233	TRUE	TRUE	TRUE	TRUE	TRUE
1234	True	True	True	True	True
1443	True	False	True	False	False

I have tried the following formula to extract the rows however, it only extract rows where a lowercase or uppercase depending on the text case i have used in the argument.

table.IsEmpty(table.FindText(table.SelectionColumns([dataset],{"bla1", "yaba2","daba3","lama","doo"}),"true")) (or using "TRUE" OR "True")

Problem statement - is there anyway we can modify this formula to check for "true" ignoring the case? or is there anyother options i can use?

Additionally, I have tried to do data normalisation and a conditional column to allocate numeric marker (1,2,3,4,5) for each column if there is "true" values (0 for false) and filter the column with false value to condence the result . It works however, I have to load the data in power query and then normalise the data to upper or lowercase which is not favourable as combine total rows of these files can go over 20mil.

I feel i might be sailing close to the answer but unable to get my hands on it.

I hope someone can help me.

Thankyou in advance for your help and support 😊

[MStaford]

Hi @danextian and@ryan_mayu,

Thankyou for your reply and suggested solutions. The minimum condition for true value to be the result is that atlease one col should have a true value and only if all appears false then the result should be false. Apologies if i was not clear in my notes above. Here is a quick sample..

Col1	Col2	col3	col4	col5	Result
true	true	true	true	true	true
true	false	true	false	true	true
true	false	false	false	false	true
false	false	false	false	false	false

@AnonymousI have not tried your method but will give it a go and come back.

[danextian · Accepted solution]

Hi @MStaford 

Still using the same code  in my initital post but changing List.AllTrue to List.AnyTrue to check if at least one of the column values contains the word true..

let
    // Step 1: Define a list of values, replace with the actual column names
    ColumnsAsAList = {[bla1], [yaba2], [daba3], [lama], [doo]},

    // Step 2: Convert each value in the list to a logical (true/false)
     ChangedType = List.Transform(ColumnsAsAList, Logical.From),
    // Step 3: Check if all values in the list are true. Use List.AnyTrue to return true if any of the values in the list  is true
    TrueOnly = List.AnyTrue(ChangedType)
in  
   TrueOnly

[Anonymous]

Hi ,

The method ryan_mayu provided should be helpful.

Besides, you can also try the following formula to add the custom column.

if Text.Contains([bla1], "true", Comparer.OrdinalIgnoreCase) or
   Text.Contains([yaba2], "true", Comparer.OrdinalIgnoreCase) or
   Text.Contains([daba3], "true", Comparer.OrdinalIgnoreCase) or
   Text.Contains([lama], "true", Comparer.OrdinalIgnoreCase) or
   Text.Contains([doo], "true", Comparer.OrdinalIgnoreCase)
then "true" else "false"

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian]

Hi @ryan_mayu 

Try this custom  column in the query editor. Use List.AnyTrue to return true if any of the values in the list  is true

let
    // Step 1: Define a list of values
    ColumnsAsAList = {[bla1], [yaba2], [daba3], [lama], [doo]},

    // Step 2: Convert each value in the list to a logical (true/false)
     ChangedType = List.Transform(ColumnsAsAList, Logical.From),
    // Step 3: Check if all values in the list are true. Use List.AnyTrue to return true if any of the values in the list  is true
    TrueOnly = List.AllTrue(ChangedType)
in  
   TrueOnly

[ryan_mayu]

@MStaford 

you can try this to create a new column to check.

= Table.AddColumn(Source, "Custom", each
[a=List.Skip(Record.ToList(_)),
b=List.RemoveItems(List.Transform(a, each Text.Lower(_)),{"true"}),
c=if List.Count(b) = 0 then true else false]
[c]
)

pls see the attachment below