The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 ?
Solved! Go to 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"
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"
Thanks alot. Works as it should