Forum Discussion

Dicken's avatar
Dicken
Icon for Post Prodigy rankPost 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...
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.

PwerQueryKees's avatar
PwerQueryKees
Icon for Super User rankSuper 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
    Icon for Post Prodigy rankPost 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