How to union items from two columns of same table

[freelensia]

I have a table with 2 columns that I want to union like this:

Column 1	Column 2	Union
apple, orange, aple	apple, lemon	apple, orange, lemon

Is there a custom formula I can write? Or do I need to make a PQ function?
It would be great to customize the delimiter as well.

Thanks for any of your ideas, @Jimmy801 @Greg_Deckler @amitchandak @parry2k @Mariusz @ImkeF 

[mahoneypat]

FYI this is a duplicate post.

You can add a custom column with this function.  Prior to this step, you can Replace Values and put a space and no value in the 2nd box to get rid of the spaces first if needed.

= Text.Combine(List.Distinct(Text.Split([Column1], ",") & Text.Split([Column2], ",")), ", ")

Pat

[freelensia]

I spoke too soon...your solution cannot handle null values for col1 or col2.

I tried to add an if statement inside but looks like I got the syntax wrong.

= Table.AddColumn(Source, "Combined", each Text.Combine(List.Distinct(if [Langs 1] = null then null else Text.Split(Text.Lower([Langs 1]), ", ") & if [Langs 2] = null then null else Text.Split(Text.Lower([Langs 2]), ", ")), ", "))

[mahoneypat · Accepted solution]

Add a step prior to this one.  Select both columns and use Replace Values to convert the nulls to blanks.

Then use this function to remove the blanks from the combined list.

= Text.Combine(List.Select(List.Distinct(Text.Split([Column1], ",") & Text.Split([Column2], ",")), each _ <> ""), ", ")

Pat