Forum Discussion
Group By data in 2 different sheets
Hi Everyone,
I want to use a function similar to SUMIF in excel. I tried many things but I'm unable to do that in power bi as I have 2 sheets of data. I also tried doing GROUPBY but that didn't work either. Is there anyway I can use any function similar to SUMIF for 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.
6 Replies
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:
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.
So something like Table 1 has a list of Material Codes by Size and Table 2 has Inventory by Pallet/location?
I'd use GroupBy on Table 2 and ensure you use correct criteria...
In example below I'm grouping by Material Code and Size (DimA and DimB) and Summing InvQty to output the Total Inventory of each item. The results would be a line for each Material/Size with the total Qty we hold in stock (so in essence =SUMIFS(InvQty,MatCode,"Material 1",DimA,"Dimension A",DimB,"Dimension B")) then merge that back into Table 1.