Forum Discussion

Dicken's avatar
Dicken
Post Prodigy
1 year ago
Solved

Power Query conditional column selection

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

Need Help
Show and Tell
  • PwerQueryKees's avatar
    PwerQueryKees
    1 year ago

    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.

8 Replies

  • slorin's avatar
    slorin
    Super User
    1 year ago

    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's avatar
      Dicken
      Post Prodigy
      1 year ago

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

      Richard. 

      • m_dekorte's avatar
        m_dekorte
        Resident Rockstar
        1 year ago

        Hi Dicken 

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

         

  • PwerQueryKees's avatar
    PwerQueryKees
    Super User
    1 year ago

    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's avatar
      Dicken
      Post Prodigy
      1 year ago

      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

  • Omid_Motamedise's avatar
    Omid_Motamedise
    Super User
    1 year ago

    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"
  • Dicken's avatar
    Dicken
    Post Prodigy
    1 year ago

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

    Thanks for taking the time to repond.

  • Dicken's avatar
    Dicken
    Post Prodigy
    1 year ago

    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) ) )