Power Query conditional column selection

[Dicken]

Hi , I wanted to select columns that contained a certain value and came up with this , but has anyone any ideas
for a different approach ? here just selectign columns  that contian "X".  BTW, should I put code in tags or something? 
I've tried using the html button but then cant type any text ? 

let
Source = #table(
type table [A = text, B = text, C = text, D = text],
{{"a,b", "X,c", "c,c", "a,d"}, {"c,c", "a,a", "a,X", "d,c"}}
),
truefalse = List.Transform(
Table.ToColumns(Source),
(x) => List.Transform(x, (y) => List.Contains(Splitter.SplitTextByDelimiter(",")(y), "X"))
),
pos = List.PositionOf(List.Transform(truefalse, (x) => List.AnyTrue(x)), true, Occurrence.All),
headers = List.Transform(pos, (x) => Table.ColumnNames(Source){x}),
result = Table.SelectColumns(Source, headers)
in
result

[Dicken]

Just to add you can skip the List.AnyTrue which does give a flexible for All / Any ; but this also works; 
= List.Transform(  Table.ToColumns( Source ), (x)=>List.ContainsAny( x, {"X"}, (x,y)=>Text.Contains(x,y) ) )

[Dicken]

Thanks for all the replies, havve nto gone through each step off all, but will

Thanks for taking the time to repond.

[Omid_Motamedise]

Hi @Dicken 

Table.UnpivotOtherColumn is really useful for this question, so use the below code

let
Source = #table(
type table [A = text, B = text, C = text, D = text],
{{"a,b", "X,c", "c,c", "a,d"}, {"c,c", "a,a", "a,X", "d,c"}}
),
    Custom1 = Table.UnpivotOtherColumns(Source,{},"Columns","Value"),
    #"Filtered Rows" = Table.SelectRows(Custom1, each Text.Contains([Value], "X"))
in
    #"Filtered Rows"

[PwerQueryKees · Accepted solution]

Your table = Source.

You can use this anywhere you would normall use SelectColumns (or RemoveColumns)

= Table.SelectColumns(Source,List.Select(Table.ColumnNames(Source), each Text.Contains(_, "X", Comparer.OrdinalIgnoreCase)))

I made the match case INsenistive, remove the Comparare.OrdinalIgnoreCase if you don't want that.

[Dicken]

Not sure it works, or Im missing something, I just got an empty 2 row table not the columns where and "X"is contained. 
let
Source = let
Source = #table(
type table [A = text, B = text, C = text, D = text],
{{"a,b", "X,c", "c,c", "a,d"}, {"c,c", "a,a", "a,X", "d,c"}} ) in Source,
Custom1 = Table.SelectColumns(Source,List.Select(Table.ColumnNames(Source), each Text.Contains(_, "X", Comparer.OrdinalIgnoreCase)))
in
Custom1

[slorin]

Hi @Dicken 

let
Source = YourSource,
DemoteHeaders = Table.DemoteHeaders(Source),
Transpose1 = Table.Transpose(DemoteHeaders),
SelectRows_with_X = Table.SelectRows(Transpose1, each not List.IsEmpty(List.FindText(List.Skip(Record.ToList(_)),"X"))),
Transpose2 = Table.Transpose(SelectRows_with_X),
PromoteHeaders = Table.PromoteHeaders(Transpose2, [PromoteAllScalars=true])
in
PromoteHeaders

 Stéphane

[Dicken]

Interesting, I'll leave it open see if anyone else repsonds,  

Richard. 

[m_dekorte]

Hi @Dicken 

Table.SelectColumns(
    Source, 
    List.Select(
        Table.ColumnNames(Source), 
        each Text.Contains(Text.Combine(Table.Column(Source, _), "|"), "X")
    )
)