how to append values of multiple columns to single columns

Used for anything related to the new Power BI Desktop and Service tools
bits
Posts: 1
Joined: Fri Apr 07, 2017 5:02 pm

how to append values of multiple columns to single columns

Postby bits » Fri Apr 07, 2017 5:14 pm

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
Attachments
test.xlsx
(8.58 KiB) Downloaded 11 times

MattAllington
Posts: 816
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: how to append values of multiple columns to single columns

Postby MattAllington » Fri Apr 14, 2017 10:42 am

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 9 times
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

PhilC
Posts: 133
Joined: Tue Sep 09, 2014 8:13 am

Re: how to append values of multiple columns to single columns

Postby PhilC » Fri May 12, 2017 1:29 pm

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"


Return to “Power BI Desktop/Service”

Who is online

Users browsing this forum: No registered users and 1 guest