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.

slorin's avatar
slorin
Icon for Super User rankSuper 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
Icon for Post Prodigy rankPost Prodigy
1 year ago

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

Richard. 

  • m_dekorte's avatar
    m_dekorte
    Icon for Resident Rockstar rankResident Rockstar
    1 year ago

    Hi Dicken 

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