Consolidate data from multiple columns

[joannerw]

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

[v-zhangti · Accepted solution]

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.

[joannerw]

Hi @v-zhangti , I've only just worked this out and it works prefectly, thank you!

[danextian]

Hi @joannerw ,

Instead of an image, can you please post your sample data as a text table? It will be easier for anyone who wants to help that way.