Forum Discussion
Transform data from multiple columns and tables into single column
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
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 42 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 columnsYou 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! 🙂
2 Replies
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 42 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 columnsYou 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! 🙂Fantastic. This worked beautifully. I did not know about creating reference tables, thank you very much for the detailed tutorial!