Transform data from multiple columns and tables into single column

Hi ctupps ,

Please perform the following steps in power query. After Step 1, I have shown you a quick method in Step 2 a) and a detailed method in Step 2 b). You can choose either based on your level of comfort with Power Query.

1) Load your tables into Power Query. 

Data 1:

Data 2:

2 a) Short version 

Create a blank query and type 
= Table.Combine({Table.UnpivotOtherColumns(Data1, {}, "Attribute", "Value"), Table.UnpivotOtherColumns(Data2, {}, "Attribute", "Value")})

Go directly to Step 4

2 b) Detailed version

Right-click on the query names "Data 1"and "Data 2" individually and click on Reference. This will create a new query which references the original query. In the referenced query, click on Transform--> Unpivot Columns-->Unpivot columns

You will see that the tables are now unpivoted. 

Data 1_transformed

Data 2_transformed

You can right click on the query names and deselect "Enable load". This will not load these tables into the report view.

3) In the next step, click on Data 1_transformed, then click on "Append Queries" --> "Append queries as new". Select the 2nd table as Data 2_transformed. 

Once you perform the append, you will see a new table like below:

4) Remove the attribute column, and rename the value column to get the final result

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂