Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Union selected column and ID

Hi, 

I have a table containing the following columns:

ID

BU1
BU2

I want to union BU1 and BU2 together so I obatain the following resultst:

DATA  
IDbusinessunit1businessunit2 
1dogBird
2NULLinsect
3horseFish
4mouseNULL
5mousebird
6BirdDog

 

 

Expected result
IDCombinedBU
1dog
3horse
4mouse
5mouse
6Bird
1Bird
2insect
3Fish
5bird
6Dog


I have solved it in DAX with the following measure table measure:

NikFe_1-1656593915631.png

 

But I need to do it in Power Query but can't figure out how to do it. 
Anyone have some suggestions for this ?



1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

Option 1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUrJTweSTplFKUqxOtFKRkCOX6iPD5DKzCtOTS4BixoDuRn5RcWpQNotszgDLGgC5OTml4IFwVpAgqZIgkkwQ81gNugouQCti40FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, businessunit1 = _t, businessunit2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"businessunit1", type text}, {"businessunit2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","NULL",null,Replacer.ReplaceValue,{"ID", "businessunit1", "businessunit2"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"ID"}, "Attribute", "CombinedBU"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Attribute", Order.Ascending}, {"ID", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Attribute"})
in
    #"Removed Columns"

Option 2 - This doesn't need any sorting

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUrJTweSTplFKUqxOtFKRkCOX6iPD5DKzCtOTS4BixoDuRn5RcWpQNotszgDLGgC5OTml4IFwVpAgqZIgkkwQ81gNugouQCti40FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, businessunit1 = _t, businessunit2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"businessunit1", type text}, {"businessunit2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","NULL",null,Replacer.ReplaceValue,{"ID", "businessunit1", "businessunit2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"businessunit2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"businessunit1", "CombinedBU"}}),
    Custom1 = Table.RemoveColumns(#"Replaced Value",{"businessunit1"}),
    #"Renamed Columns1" = Table.RenameColumns(Custom1,{{"businessunit2", "CombinedBU"}}),
    #"Appended Query" = Table.Combine({#"Renamed Columns", #"Renamed Columns1"}),
    #"Filtered Rows" = Table.SelectRows(#"Appended Query", each ([CombinedBU] <> null))
in
    #"Filtered Rows"

View solution in original post

2 REPLIES 2
Vijay_A_Verma
Super User
Super User

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

Option 1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUrJTweSTplFKUqxOtFKRkCOX6iPD5DKzCtOTS4BixoDuRn5RcWpQNotszgDLGgC5OTml4IFwVpAgqZIgkkwQ81gNugouQCti40FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, businessunit1 = _t, businessunit2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"businessunit1", type text}, {"businessunit2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","NULL",null,Replacer.ReplaceValue,{"ID", "businessunit1", "businessunit2"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"ID"}, "Attribute", "CombinedBU"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Attribute", Order.Ascending}, {"ID", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Attribute"})
in
    #"Removed Columns"

Option 2 - This doesn't need any sorting

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUrJTweSTplFKUqxOtFKRkCOX6iPD5DKzCtOTS4BixoDuRn5RcWpQNotszgDLGgC5OTml4IFwVpAgqZIgkkwQ81gNugouQCti40FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, businessunit1 = _t, businessunit2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"businessunit1", type text}, {"businessunit2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","NULL",null,Replacer.ReplaceValue,{"ID", "businessunit1", "businessunit2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"businessunit2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"businessunit1", "CombinedBU"}}),
    Custom1 = Table.RemoveColumns(#"Replaced Value",{"businessunit1"}),
    #"Renamed Columns1" = Table.RenameColumns(Custom1,{{"businessunit2", "CombinedBU"}}),
    #"Appended Query" = Table.Combine({#"Renamed Columns", #"Renamed Columns1"}),
    #"Filtered Rows" = Table.SelectRows(#"Appended Query", each ([CombinedBU] <> null))
in
    #"Filtered Rows"
Anonymous
Not applicable

Thanks alot. Works as it should

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors