Forum Discussion
Solved
Filter Columns based on columns' name
Hi all,
May I ask is there any way I could filter columns based on its name?
| ID | Group | Cost 1 | Cost 2 | Price 1 | Price 2 |
| 1 | A | - | 0.8 | - | -0.11 |
| 2 | B | 0.4 | - | - | 0.23 |
| 3 | - | 0.5 | - | 0.6 | - |
| 4 | C | - | 0.4 | 0.1 | - |
| 5 | D | 0.6 | 0.2 | 0.54 | 0.57 |
In this case, is it possible to filter out "-" if columns' name contains "Price"?
I am trying this but still fail,
= Table.SelectRows(Source, each (List.Select(Table.ColumnNames(Source), each Text.Contains(_,"Price") <> "-")))
Anonymous if you could help?
= let col=List.Select(Table.ColumnNames(Source), each Text.Contains(_,"Price")) in Table.SelectRows(Source, each List.PositionOf(Record.ToList(Record.SelectFields(_,col)),"-")<0)
4 Replies
= let col=List.Select(Table.ColumnNames(Source), each Text.Contains(_,"Price")) in Table.SelectRows(Source, each List.PositionOf(Record.ToList(Record.SelectFields(_,col)),"-")<0)wdx223_Daniel may I have a further question?
Is it possible to achieve a similar outcome:
When the Columns name contains "price" then Change "Type number"
Great thanks if you have any idea!
=Table.TransformColumnTypes(PreviousStep,List.Transform(List.Select(Table.ColumnNames(PreviousStep),each Text.Contains(_,"price")),each {_,type number}))
wdx223_Daniel Thanks so much. It works perfectly
