Union selected column and ID

[Anonymous]

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
ID	businessunit1	businessunit2
1	dog	Bird
2	NULL	insect
3	horse	Fish
4	mouse	NULL
5	mouse	bird
6	Bird	Dog

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

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

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

[Vijay_A_Verma · Accepted solution]

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]

Thanks alot. Works as it should