Power Query Select Columns based on column content

[Dicken]

I have come up with this way to select columns based on text content ( cat , dog) , but it involves nested
List.Transforms, so I wondered if anyone has a different approach , I have attached the workbook with two examples,

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ToCol = Table.ToColumns( Source ),
    Split_contain = List.Transform(  ToCol, (A)=> 
   List.Transform(A, (B)=> List.ContainsAny( Splitter.SplitTextByWhitespace() (B), {"cat","dog"} ) ) ),
    TrueFalse = List.Transform(  Split_contain, (x)=> List.AnyTrue(x)),
    Positions = List.PositionOf( TrueFalse, true, Occurrence.All ),
    ColNames = List.Transform(  Positions , (A)=> Table.ColumnNames( Source ){A} ),
    Custom7 = Table.SelectColumns( Source , 
 ColNames )
in
    Custom7

If anyone has any thought on a different approach I would be interested.

Richard.

[romperstomper]

You could use something like:

Table.SelectColumns(Source, List.Accumulate(Table.ColumnNames(Source), {}, (s, c)=>if List.Count(List.Select(Table.Column(Source, c), each Text.Contains(_, "dog") or Text.Contains(_, "cat"))) > 0 then s & {c} else s))

[AlienSx]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    headers = List.Buffer(Table.ColumnNames(Source)), 
    contains = (lst, var) => List.Contains(lst, var, (x, y) => Text.Contains(x, y)),
    columns = Table.ToColumns(Source), 
    poz = List.PositionOfAny(
        columns, 
        {"cat", "dog"}, 
        Occurrence.All, 
        (x, y) => contains(x, y)
    ), 
    select = Table.SelectColumns(
        Source,
        List.Transform(poz, (x) => headers{x})
    )
in
    select

[Dicken]

Thanks, have not had a chance to go through it step by step but looks good.

RD

[AlienSx]

yet another option

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    headers = List.Buffer(Table.ColumnNames(Source)), 
    columns = Table.ToColumns(Source), 
    poz = List.PositionOf(
        columns, 
        {"cat", "dog"}, 
        Occurrence.All, 
        (x, y) => List.ContainsAny(x, y, (w, z) => Text.Contains(w, z))
    ), 
    select = Table.SelectColumns(
        Source,
        List.Transform(poz, (x) => headers{x})
    )
in
    select

[Dicken]

Ok, I'll close this that is enough to be going on with , thanks again, 😉

[Dicken]

Just to say thanks again, I have not yet gone through the second version,
but after the first I came up with this

= (alist as list , valuelist  as list  )=>  
List.ContainsAny( 
    alist  , valuelist  , 
(x,y)=> Text.Contains( x,y ) )

I think this is close to your second , would never have got this without the help.