Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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?
Thanks for any of your ideas, @Jimmy801 @Greg_Deckler @amitchandak @parry2k @Mariusz @ImkeF
Solved! Go to Solution.
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.
Simple method, but this does not give me distinct lists. Thanks though!
Sorry, I didn't read the specs right 🙂 however, starting from this result you can use functions
Text.Split
List.Distinct
and
Text.Combine
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.
Hi @mahoneypat your solution looks simple. Works. I added Text.Lower to ignore upper vs. lowercase differences. I also changed the delimiter to ", "
Btw where is the duplicate of my post?
@freelensiatry this
let
Source = Web.BrowserContents("https://community.powerbi.com/t5/Power-Query/How-to-union-items-from-two-columns-of-same-table/m-p/1642958#M50238"),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE:nth-child(3) > * > TR > :nth-child(1)"}, {"Column2", "TABLE:nth-child(3) > * > TR > :nth-child(2)"}, {"Column3", "TABLE:nth-child(3) > * > TR > :nth-child(3)"}}, [RowSelector="TABLE:nth-child(3) > * > TR"]),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column 1", type text}, {"Column 2", type text}, {"Union", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1","apple, orange, aple","apple,orange,apple",Replacer.ReplaceText,{"Column 1"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","apple, lemon","apple,lemon",Replacer.ReplaceText,{"Column 2"}),
#"Trimmed Text" = Table.TransformColumns(#"Replaced Value1",{{"Column 1", Text.Trim, type text}, {"Column 2", Text.Trim, type text}}),
#"Added Custom2" = Table.AddColumn(#"Trimmed Text", "Custom.2", each List.Distinct(List.Combine({List.Distinct(Text.Split([Column 1],",")),List.Distinct(Text.Split([Column 2],","))}))),
#"Extracted Values" = Table.TransformColumns(#"Added Custom2", {"Custom.2", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
#"Extracted Values"
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
8 | |
7 | |
6 |