How to Merge data from multiple columns to one?

[mithunt]

Hello Powerbians...

I need some assistance

I have created 5 Conditional Columns and want to merge all of them to one.

Col 1 - Shirt Size - S,M,L,XL,XXL, Other
Col 2 - Tshirt size - 38,40,42,44,Other

etc,

Output should look like

S
M
L
XL
XXL
38
40
42
44
Other

If the data is not matching in any of the columns, then it should be marked as Other

Thanks in Advance

[v-jingzhang · Accepted solution]

Hi @mithunt 

Here is another solution you can try. 

Original table:

Add a custom step: (#"Changed Type" is the previous step name) 

= Table.FromList(List.Select(List.Distinct(List.Combine(Table.ToColumns(#"Changed Type"))), each _ <> ""))

Result:

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Mahesh0016]

@mithunt 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WClbSUTK2UIrViVbyBTJNDMBMHxDTCMyMALNNIGwwx78kI7UIzIewdJSUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1", type text}, {"Column 2", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column 1] <> "Other")),
#"Transposed Table" = Table.Transpose(#"Filtered Rows"),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Transposed Table", {}, "Attribute", "Value")
in
#"Unpivoted Columns"

@mithunt If this post helps, please consider accept as solution to help other members find it more quickly and Appreciate your Kudos.