Forum Discussion
Consolidate data from multiple columns
Below is an example of a dataset that I need to consolidate to be able to report on each weed type, each chemical type and the total and average of the concentrate. How do I consolidate this data? There is more data on each line, but this is the data that is entered more than once
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.
3 Replies
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.
