Forum Discussion
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 | ||
| 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 ?
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"
2 Replies
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"