Forum Discussion
freelensia
Advocate II
Advocate IIHow to union items from two columns of same table
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?...
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
freelensiaAdvocate II
Advocate III 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
Microsoft Employee
Microsoft EmployeeAdd 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