Forum Discussion

Helper I

3 years ago

Solved

How to Remove Entire Values Based on Condition?

I have a column like so:

Cost Center

CC0328:

PG12312

PG12333

PG09862

PG12312

CC0223:

PG23423

PG76732

PG92332

I'm looking to remove all the values that start with "PG" so that I am only left with values that start with "CC".

I've tried the following code but it's not working as expected:

= Table.ReplaceValue(#"Renamed Columns",
each "Cost Center",
each if Text.Start("PG") then "" else false,
Replacer.ReplaceText,{"Cost Center"})

How can I properly remove all values that start with "PG"?

Thank you.

johnt75
3 years ago
Ah, OK. Add a new conditional column

= Table.AddColumn(#"Changed Type", "Custom", each if Text.StartsWith([Cost Center], "PG") then null else [Cost Center])

then you can delete the original column and rename the new one to Cost Center

3 Replies

johnt75
Super User
3 years ago
Click the down arrow in the header of the column you want to filter and choose Text Filters -> Starts With, or Does not start with
- christianunr
  Helper I
  3 years ago
  That removes the entire row from the table when I'm in the data tab. I still need to keep the rows.
  - johnt75
    Super User
    3 years ago
    Ah, OK. Add a new conditional column
    
    = Table.AddColumn(#"Changed Type", "Custom", each if Text.StartsWith([Cost Center], "PG") then null else [Cost Center])
    
    then you can delete the original column and rename the new one to Cost Center

Related content