Remove Values from CellQ

[Spyderturbo007]

I have a column that I need to break up based on the _ deliminator. It's supposed to be formatted like this:

SKU_DesignID_DecorationType

But, I have people that sometimes forget to add the _DecorationType.

So when I use Power Query to split the cell at the last _ sometimes I get _DesignID because that's the last delimiter. Is there a way to remove values that aren't part of a list of set values? That would split the cell and then delete the cell values that aren't in the list. I don't want to remove the rows, just remove the offending cell values.

Here is the value without the _DecorationType



This is after the column split:



Here is the full list of values I want to keep. Everything else would need to be blank.



I might be able to do this by removing all non-text values because what I want to keep never contains a number. I'm not sure of the best way to handle the issue.

Thank you!

 

[Kaper]

One of the concepts could be to split on all delimiters (not just rightmost) and then re-join first two. Something like:

.......
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "SKU", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"SKU.1", "SKU.2", "SKU.3"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Split Column by Delimiter", "Merged", each Text.Combine({[SKU.1], "_", [SKU.2]}), type text)
in
    #"Inserted Merged Column"

(and in SKU.3 there will be either null or your DecorationType, next step would be probably deleting columns SKU.1 and SKU.2 (or may be use them for sorting or some other actions)

 

[Spyderturbo007]

Thank you for the help. I hate asking this question, but where do I paste the query you created? I tried putting it in the Custom Column dialog box, but got an error. I found another section that says "Advanced Editor", but there is already data in there. Sorry for the basic question, but this is entirely new to me.

 

[Kaper]

It is perfectly OK to ask. The only problem is time-to-answer on such forum as this one
Indeed, you can insert the code using the advanced editor. Just replacing part of the existing code (probably just one line) with two lines which are between the dots and keyword "in" in post above.
But there are some rules to be observed. Especially: names of the columns and names of the steps have to be matched both in these 2 lines and probably line(s) below.

Another option is (especially if you created the query yourself is to create it again using similar method. The code above is basically clicked in editor. The only thing written was in add custom column from the example step, where I wrote for first line FirstRowSKU_FirstRowDesignID (PC...._1666....) and PQ proposed properly CP82_1666... as second entry in this column and when I agreed the Merged column was created. It is probably the easiest way.

Yet another option is to edit existing query also by using standard "cliking" method. Next to a step with splitting action you click on a gear icon. The dialog which was originally used to choose detatils will be showed again, and you change parameters of splitting from rightmost delimiter only to all occurences. And then insert a step acc custom column (from example).

Of course do a backup before using any of the methods . Undo is quite limited in PQ . And if you notice a problem before closing PQ, you can choose "discard changes", but if you notice problem later, the backup is real "life saver" .

If above tips do not help, you may attach a PQ code (as visible in advanced editor). (if there are details of connection in Source line, you can delete them).