Transform data from multiple columns and tables into single column

[ctupps]

Hello,

I have two datasets, lets call them Data1 and Data2. Data1 is actively being refreshed with new data, but Data2 is static. Both have multiple columns containing string values that I'd like to display on a dashboard as a table with a single column containing all the values from both tables. The datasets do not currently have any relationship, and each cell contains a unique value that I'd like displayed in a new row in the resulting table. See example below:

Data1

column_1    column_2    column_3

dog             cat                mouse

bird             turtle            squirrel

Data2

column_A      column_B      column_C

lemur             snake            giraffe

monkey          lion               hippo

New Table

new_column

dog             

cat               

mouse

bird           

turtle           

squirrel

lemur             

snake            

giraffe

monkey         

lion               

hippo

The resulting table should be able to add new rows of data as the Data1 connection is refreshed. I am unsure whether the best way to accomplish this is by creating a new table or a filter, but if possible I'd like to keep the final result separate from the original data tables. Any advice is appreciated.

Thanks in advance!

Cara

[ctupps]

Fantastic. This worked beautifully. I did not know about creating reference tables, thank you very much for the detailed tutorial!

[rohit_singh · Accepted solution]

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! 🙂