Group By data in 2 different sheets

[Anonymous]

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.

[JamesRobson]

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.

[JamesRobson]

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:

https://www.google.com/search?q=power+query+sumif+columns&safe=active&rlz=1C1GCEB_enGB927GB927&ei=fH...

[Anonymous]

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.

[v-jingzhang · Accepted solution]

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.

[JamesRobson]

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,

[Anonymous]

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.