Forum Discussion
Consolidate data from multiple columns
Hi, joannerw
You can try the following methods. In Power Query, copy the tables into 3 copies, leaving only 3 columns for each table.
Table1 and Table2:
Select the Weed 1 column, Chemical 1 column and Concentration 1 column in Table 1 and click Unpivot Column. The result is shown in the figure.
Other tables are also set up like this.
Then use the Append function to add several tables to one table.
Result:
Let's go back to Desktop. Modify the column name.
From:
To:
Finally, a new table is created.
Chemical = SUMMARIZE(Append1,Append1[Chemical],Append1[Value.2])Weed = SUMMARIZE(Append1,Append1[Weed],Append1[Value.1])Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, joannerw
You can try the following methods. In Power Query, copy the tables into 3 copies, leaving only 3 columns for each table.
Table1 and Table2:
Select the Weed 1 column, Chemical 1 column and Concentration 1 column in Table 1 and click Unpivot Column. The result is shown in the figure.
Other tables are also set up like this.
Then use the Append function to add several tables to one table.
Result:
Let's go back to Desktop. Modify the column name.
From:
To:
Finally, a new table is created.
Chemical = SUMMARIZE(Append1,Append1[Chemical],Append1[Value.2])
Weed = SUMMARIZE(Append1,Append1[Weed],Append1[Value.1])
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
