SUMIFS WITH POWER QUERY

[Mic1979]

Dear all,

I have the following case:

1. In the table "Manifold_Distribution", I need to add a column matching the cell values, for each line, with what is in the table "OVERALL_DISTRIBUTION".
2. I am now making this operation with the following excel formula: 

SUMIFS(OVERALL_DISTRIBUTION[Volumes Y3],OVERALL_DISTRIBUTION[Region],[@Region],OVERALL_DISTRIBUTION[Type],[@Type],OVERALL_DISTRIBUTION[Valve_Function],[@[Valve_Function]],OVERALL_DISTRIBUTION[Integrated_Check_Valve],[@[Integrated_Check_Valve]],OVERALL_DISTRIBUTION[Connection_Type_Front_Port],[@[Connection_Type_Front_Port]],OVERALL_DISTRIBUTION[Connection_Type_Side_Ports],[@[Connection_Type_Side_Ports]],OVERALL_DISTRIBUTION[Connection_Material],[@[Connection_Material]],OVERALL_DISTRIBUTION[Bonnet_Material],[@[Bonnet_Material]])

Is it possiblwe to do the same in Power Query?

Here you have the sample file to better show the request.

https://docs.google.com/spreadsheets/d/10WNjRxf4Hujx0NiUoJI9vSexQG6toEuS/edit?usp=drivesdk&ouid=1059...

Many thanks.

[Anonymous · Accepted solution]

Hi @Mic1979 
Thank you for reaching out microsoft fabric community forum.

You can use the Table.Group function to get the same result in Power Query. Instead of merging the tables first, you’d start by grouping the OVERALL_DISTRIBUTION table based on all the matching criteria columns like Region, Type, Valve_Function, etc., and summing the Volumes Y3 values for each group.

To do this, load both tables into Power Query. In OVERALL_DISTRIBUTION, use the Group By option to group by all relevant columns and sum the Volumes Y3 column. Once you have the grouped data, merge it with Manifold_Distribution using the same columns as the matching criteria. Finally, expand the merged table to bring in the summed Volumes Y3 values, and load the data back into Excel.

This method is more efficient and avoids unnecessary duplication.

If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community
Thank you.

[Mic1979]

Thanks for your suggestion.

[Mic1979]

Dear all,

no suggestion here about how to proceed?

Thanks.

[Mic1979]

Thanks.

what about using Table.Group function?

[bhanu_gautam]

@Mic1979 

Load the "Manifold_Distribution" table:

Go to the "Data" tab in Excel.
Click on "Get Data" > "From Other Sources" > "From Table/Range".Select the range of your "Manifold_Distribution" table and click "OK".
Load the "OVERALL_DISTRIBUTION" table:

Repeat the steps above to load the "OVERALL_DISTRIBUTION" table.

Merge the tables:

In the Power Query Editor, select the "Manifold_Distribution" query.
Go to the "Home" tab and click on "Merge Queries".
Select the "OVERALL_DISTRIBUTION" table as the second table to merge with.
Select the columns that you want to use as matching criteria from both tables (e.g., "Region", "Type", "Valve_Function", etc.).
Click "OK" to create the merged table.

Expand the merged table:

Click on the expand icon (two arrows) next to the merged column.
Select the "Volumes Y3" column to include it in the "Manifold_Distribution" table.
Click "OK".

Add a custom column to calculate the sum:

Go to the "Add Column" tab and click on "Custom Column".
Enter a name for the new column (e.g., "Sum Volumes Y3").
Use the following formula to calculate the sum:

List.Sum([MergedColumn][Volumes Y3])

Close and load the data back to Excel: