Forum Discussion
Group By data in 2 different sheets
Hi Anonymous
You can use Merge Queries feature to bring data from one table into another table. Select one or multiple matching column pairs in both tables for joining. Column headers don't need to match between tables. However, it's important to note that the columns must be of the same data type, otherwise the merge operation might not yield correct results. For example,
After selecting OK in the Merge dialog box, a new column will be added to the base table with the same name as your right table. This column holds the values corresponding to the right table on a row-by-row basis. From this new table column, click the expand icon on column header and switch to Aggregate. For the field you want to sum, select Sum aggregation type for it.
After selecting OK, this field will be sumed up according to the matching columns.
For detailed guidance, please refer to Merge queries overview
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Not 100% sure you're after so can you give an example? Groupby is pretty effective when used correctly and why 2 seperate sheets, can you make a temporary table to do the sum then pull back to the original?
In my experience SUMIF's in Power Query are really difficult to achieve and put such strain on the report load times becomes ridiculous, to the point I've always found a better work around, however there is a video here to explain how it can be done:
I have 2 different datasets of different sizes and the 2nd dataset has multiple rows of the same entity whose value I want from it it. So I have to do a SUMIF to get the overall value from the second sheet. I've looked at the video you sent but that did not work for me.
That isn't happening because my Sheet 2 has more rows and columns than sheet 1 and Groupby does not let me take a variable from the second sheet.
Hi Anonymous
You can use Merge Queries feature to bring data from one table into another table. Select one or multiple matching column pairs in both tables for joining. Column headers don't need to match between tables. However, it's important to note that the columns must be of the same data type, otherwise the merge operation might not yield correct results. For example,
After selecting OK in the Merge dialog box, a new column will be added to the base table with the same name as your right table. This column holds the values corresponding to the right table on a row-by-row basis. From this new table column, click the expand icon on column header and switch to Aggregate. For the field you want to sum, select Sum aggregation type for it.
After selecting OK, this field will be sumed up according to the matching columns.
For detailed guidance, please refer to Merge queries overview
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.Hi Jing,
Question on the above method, will this laod quicker than a seperate Merge and GroupBy? Didn't know you could do it this way, which is much easier, but wonder if there was any impact on performance of large datasets?
Thanks,