Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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]), ", ")), ", "))
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.