Help with Splitting a column

[colmac]

Wonder if someone can help a complete Newb at Power Query.

I'm trying to analyse several years worth of banking data. I need to allocate a spending type to each payee. The payee description field is particularly unhelpful, but much of it follows 2 main patterns.

The first has simply a text entry for the company name eg Ebay Sarl, Paypal etc (see attached image (after split into 2 columns)

The second has a variable number of entries separated by " , "

Using Split column in Power query, I have isolated most of these entries by splitting into 2 columns at the leftmost Delimiter which for all these entries seems to give me what I want. Unfortunately all the Text only entries have been kept in the first of the split columns (see attached image).

Is there any way to exclude those "text only" entries from the split so I can end up with all those payees in a single column, ie no null entries in that split second column.

Thanks

Colin

Hi,

[colmac]

Thanks for such a quick and helpful reply.

Worked perfectly - mind you I suspect you knew it would!

Many thanks though 

[BA_Pete · Accepted solution]

Hi @colmac ,

For the sake of simplicity you could just add a third column:

if [Column2] = null then [Column1] else [Column2]

If you want to do it inline, you could right-click a null cell in [Column2], go to Replace Values, replace with "aaa", then amend the auto-generated code in the formula bar to change "aaa" to [Column1].

Pete