how to append values of multiple columns to single columns

[bits]

Hi

I need to append value of multiple columns from a table/sheet in to a single column like below example in powerBI. (check attachment plz)
• col3 value and col4 values to be append to in 1 column col3n
• col5 value and col6 values to be append to in 1 column col4n
• Values of col1 and col2 to be repeated accordingly


Before:
col1 col2 col3 col4 col5 col6
1 a 11 12 13 14
2 b 21 22 23 24
3 c 31 32 33 34


After Transform:
col1 col2 col3n col4n
1 a 11 12
1 a 13 14
2 b 21 22
2 b 23 24
3 c 31 32
3 c 33 34


Thanks

[MattAllington]

I think the easiest way is to
1. create a link to your data - do not load
2. reference 1 above and remove columns 5 and 6 - do not load
3. reference 1 above and remove columns 3 and 4. Rename column 5 to be column 3 and column 6 to be column 4 - do not load
4. append tables 2 and 3 together

Here it is

append.pbix

(160.7 KiB) Downloaded 66 times

[PhilC]

This also works:

Code: Select all

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"col1", Int64.Type}, {"col2", type text}, {"col3", Int64.Type}, {"col4", Int64.Type}, {"col5", Int64.Type}, {"col6", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"col1", "col2"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Group", each if [Attribute]="col3" or [Attribute]="col4" then 1 else 2),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom","col5","col3",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","col6","col4",Replacer.ReplaceText,{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value1", List.Distinct(#"Replaced Value1"[Attribute]), "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Group"})
in
    #"Removed Columns"