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?
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"
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.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |