Append data from one column to another in power query

[theyaaz]

I am using power query to transform a data set. I have two sets of separate columns which need to be appended to one another, while copying over the data from other corresponding columns. This is what I currently have:

MrExcel Help.xlsx
	A	B	C	D	E	F	G	H
1	Employee ID	Employee Name	Gender	Month	Attribute1	Value1	Attribute2	Value2
2	1245	Julio	M	01-Jan	Benefits	$ 235.00	Salary Increase	$ -
3	3475	Julio	M	01-Jan	Overtime	$ 150.00	Benefits Weighted	$ 340.00
4	3425	Marge	F	01-Feb	Benefits	$ 225.00	Salary Increase	$ -
5	2342	Marge	F	01-Feb	Overtime	$ 170.00	Benefits Weighted	$ 400.00
6	1242	Gustav	M	01-Mar	Benefits	$ 300.00	Salary Increase	$ -
7	1515	Gustav	M	01-Mar	Overtime	$ 190.00	Benefits Weighted	$ 150.00
8	1465	Marsha	F	01-Apr	Benefits	$ 340.00	Salary Increase	$ 400.00
9	1277	Marsha	F	01-Apr	Overtime	$ 250.00	Benefits Weighted	$ 230.00
Have

This is what I want my data to look like:

MrExcel Help.xlsx
	A	B	C	D	E	F
1	Employee ID	Employee Name	Gender	Month	Attribute1	Value1
2	1245	Julio	M	01-Jan	Benefits	$ 235.00
3	3475	Julio	M	01-Jan	Overtime	$ 150.00
4	3425	Marge	F	01-Feb	Benefits	$ 225.00
5	2342	Marge	F	01-Feb	Overtime	$ 170.00
6	1242	Gustav	M	01-Mar	Benefits	$ 300.00
7	1515	Gustav	M	01-Mar	Overtime	$ 190.00
8	1465	Marsha	F	01-Apr	Benefits	$ 340.00
9	1277	Marsha	F	01-Apr	Overtime	$ 250.00
10	1245	Julio	M	01-Jan	Salary Increase	$ -
11	3475	Julio	M	01-Jan	Benefits Weighted	$ 340.00
12	3425	Marge	F	01-Feb	Salary Increase	$ -
13	2342	Marge	F	01-Feb	Benefits Weighted	$ 400.00
14	1242	Gustav	M	01-Mar	Salary Increase	$ -
15	1515	Gustav	M	01-Mar	Benefits Weighted	$ 150.00
16	1465	Marsha	F	01-Apr	Salary Increase	$ 400.00
17	1277	Marsha	F	01-Apr	Benefits Weighted	$ 230.00
Want

I am new to power query, but have not had any luck with searching or experimentation. Thank you.

 

[smozgur]

It could be done by using the following M Code.

Excel Ribbon -> Data -> Get Data -> From Other Sources -> Blank Query. Power Query is loaded with a blank query.
Click Advanced Editor on the Power Query Ribbon.
Delete all content of the editor that pops up, and copy and paste the following code into the editor.
Click Done to see the result.
Note: Assuming you already converted the source data range to a table and named it as Table1. If your table is named differently, then change the "Table1" accordingly in the code.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TableAttr1 = Table.SelectColumns(Source,{"Employee ID", "Employee Name",  "Month", "Gender", "Attribute1", "Value1"}),
    TableAttr2 = Table.SelectColumns(Source,{"Employee ID", "Employee Name",  "Month", "Gender", "Attribute2", "Value2"}),
    RenameColumns = Table.RenameColumns(TableAttr2,{{"Attribute2", "Attribute1"}, {"Value2", "Value1"}}),
    Result = Table.Combine({TableAttr1, RenameColumns}) 
in
    Result

How to do it by using the interface instead of using M Code?

Select the table in Excel. Get Data -> From Other Sources -> From Table or Range.
Right-click on the query in the editor (Table1 is the query name if the table name in Excel is Table1) and Duplicate.
(We are going to append tables. )

Click on the initial query, Table1, and select Attribute2 and Value2 columns (by using the Control key to make multiple column selections). Then click Remove Columns.

Select the second duplicated query and do the same in it for Attribute1 and Value1 columns this time.
Note: You can alternatively use the command menu that will appear on right-click on any selected column for the Remove Columns command.

While the duplicated query is still selected, rename Atrribute2 and Value2 columns to Attribute1 and Value1 by using the Rename command in the right-click menu for both columns, so they should match with the columns in the first query since we will append one table to another and the column names must match to get the desired result.
Note: You can also double-click on a column name to rename it.

Select Table1 and click Append Queries:

Select "Table1 (2)" as the Table to append in the Append dialog, and click Done.

"Table1" now contains the combined result.

 

[Kerryx]

Maybe try this way
1. Import tabel to PQ and duplicate it
On new table apply the following steps

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee ID", Int64.Type}, {"Employee Name", type text}, {"Gender", type text}, {"Month", Int64.Type}, {"Attribute1", type text}, {"Value1", Int64.Type}, {"Attribute2", type text}, {"Value2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Attribute1", "Value1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute2", "Attribute1"}, {"Value2", "Value1"}})
in
    #"Renamed Columns"

On original table apply then apply the following
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee ID", Int64.Type}, {"Employee Name", type text}, {"Gender", type text}, {"Month", Int64.Type}, {"Attribute1", type text}, {"Value1", Int64.Type}, {"Attribute2", type text}, {"Value2", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Attribute2", "Value2"}),
#"Appended Query" = Table.Combine({#"Removed Columns", #"Table1 (2)"})
in
#"Appended Query"

 

[Kerryx]

Oops sorry same as smozgur he got there first