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 28 times

MattAllington
Posts: 898
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 27 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: 145
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 5 guests