• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Can I perform SUMIFS in Power Query without a lot of fuss?|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Can I perform SUMIFS in Power Query without a lot of fuss?|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryCan I perform SUMIFS in Power Query…
sp_PrintTopic sp_TopicIcon
Can I perform SUMIFS in Power Query without a lot of fuss?
Avatar
Scotty81
Member
Members
Level 0
Forum Posts: 36
Member Since:
April 25, 2017
sp_UserOfflineSmall Offline
1
September 14, 2018 - 5:38 am
sp_Permalink sp_Print

OK, I'm a little embarrassed in asking this question because it is a trivial operation in Excel.  And, I know one can do conditional logic in Power Pivot.  But, since I need to stay in the Power Query world, as I have a lot of data machinations to perform, I'm looking for a solution in that world.  In particular, I'm looking to calculate subtotals [in an new column], by adding up values in another column based on criteria in a 3rd column.  For example, if I was summing data in column B in Excel, for values in column A that matched the text "my criteria", the Excel formula would be: =SUMIF(A:A,"my criteria",B:B).  If I had a table of data and added this formula into all the rows, I would get the same sum for any row that had the phrase "my criteria" in it.  that is the behavior that I'm looking for.  Here are some potential solutions I've found....

1) One can do a group by in Power Pivot, but that won't work for me since that would aggregate the original data without adding an additional column.  I don't want to reduce any rows, but rather add a column of data with the subtotals. 

2) I've seen some solutions on some Excel websites where one can write custom functions to do this.  That is a viable solution, but I feel that is a little overkill for what I'm trying to do.

3) One can copy (i.e. reference) the initial query, and perform the "Group By" operations to obtain the subtotals.  Then, one can join the original query (containing the raw data) with the referenced query.  This will essentially do a lookup into that second table to get the desired subtotal for every row in the first table. 

Option 3 is what I plan to do, and although that is a totally viable solution, it just seems to me that it results in one unnecessary query.   I already have quite a few queries, and need to add several subtotals in various tables, so I might end up adding 5 or so more queries just for this type of solution.  Since I'd like to keep my queries to a minimum, I was hoping for a more elegant approach that eliminates the need to write an extra query.

I figured that if it was possible, or easy in Power Query, it would have been covered in the Power Query course.  I'm posting this question in the forum just in case there was something easy I missed in the lectures.  🙂

Thanks,

Cory

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4620
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
September 14, 2018 - 1:45 pm
sp_Permalink sp_Print

Hi Cory,

The method you describe in option 3 is what I would do.

Less queries or less steps does not always = more efficient. The benefit of using option 3 is a) it's dead easy to do with the GUI, b) it's transparent to anyone who picks up the file later, c) it's easy to modify.

Power Query will push the work back to the server your database is on if it's something that can be done there rather than by Excel, so it could still be just as efficient than any of the other methods.

I'd go with it.

Mynda

Avatar
Blanka Blair
Member
Members
Level 0
Forum Posts: 53
Member Since:
October 17, 2015
sp_UserOfflineSmall Offline
3
September 14, 2018 - 11:40 pm
sp_Permalink sp_Print

Hi Cory,

See if this is what you need in the attached file.

If I understand your question correctly, I had a similar problem and I used AllRows in my grouping.

Blanka

sp_AnswersTopicAnswer
Answers Post
Avatar
Scotty81
Member
Members
Level 0
Forum Posts: 36
Member Since:
April 25, 2017
sp_UserOfflineSmall Offline
4
September 15, 2018 - 3:30 am
sp_Permalink sp_Print

Mynda - Thank you for your pragmatic reply.  At least I wasn't off base in suggesting my proposed course of data manipulation.

Blanka - Thank you too for your reply.  The operations you show in your example are exactly what I was trying to do, and now I can do that without an extra query.  As Mynda says, an extra query may not be less efficient, but your method is more elegant in that it can be done inside the same query.  Plus, I learned about the new All Rows functionality that I wasn't aware of before.  🙂

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4620
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
5
September 15, 2018 - 7:27 am
sp_Permalink sp_Print

Great solution, Blanka! I've never noticed the All Rows option in the group dialog. I learnt something new too 🙂

Mynda

Avatar
nutmeg82

New Member
Members
Level 0
Forum Posts: 2
Member Since:
June 24, 2019
sp_UserOfflineSmall Offline
6
June 24, 2019 - 9:50 pm
sp_Permalink sp_Print

Hi all,

Touching on this subject again, I have a similar scenario based on 400,000 rows of data and growing day by day. The data source is 15 CSV files which are appended, transformed, and merged, with quite a lot of steps in Power Query and then exported to a data model in Excel. This enables me to create numerous pivot tables and charts to create distinct counts and sums of the data for several dashboards in the workbook.

My issue now is how complex and and how many queries I'm needing to run simultaneously - which are ultimately duplicates of the orginal 'consolidated' query to create my sumifs based on alternative criteria. What I mean by this is that this project started off as we "just want a total value of x", then "can you break it down by period?", then by country, then by region, then by user etc. So it meant I was duplicating and creating views of the main query just so i could have a pivot tables/charts in different variables.

In PowerBI, Tableau etc, this is a lot easier as it can aggregate the data accordingly based on the visibile data. I'm trying to do the same in Excel via Power Query which is when I stumbeld upon this forum thread. Am just wondering what the cleanest and most efficient (refresh speed/time) and method is. My thought reading this thread is I need to create seperate individual queries for the sumifs/subtotals and merge with the main 'consolidated' query, to create addtional colums which would be:

Total all/county/region/user

Period total all/country/region/user 

Is this the most efficient method?

Thanking you in advance

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1845
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
7
June 26, 2019 - 12:16 am
sp_Permalink sp_Print

Hi,

Please keep in mind that Power Query is designed for data import and transformations, not for calculations and reports. For large datasets, the next natural step after the data is imported and converted, is to use in in the data model, in Power Pivot, to create powerful reports. Power BI has the same tools: Power Query and Power Pivot.

Avatar
nutmeg82

New Member
Members
Level 0
Forum Posts: 2
Member Since:
June 24, 2019
sp_UserOfflineSmall Offline
8
June 26, 2019 - 7:12 pm
sp_Permalink sp_Print sp_EditHistory

Hi Catalin - thanks for your response.

 

My organisation uses Microsoft Office Standard 2013 - so I do not have access to Power Pivot unfortunately. We need to bulld the dashboard in Excel. Apologies if am asking silly questions but only started using Power Query recently because of this project.

 

So you are recommending that as opposed to duplicating or creating references of the 'main query' to direct from Power Query to create alterative views/calculations to merge with the main for sumifs, subtotals etc., you suggest I use the Data Model in Excel instead? The reason I am slightly confused by this as goes against the responses in this thread from Blanka, Corey and Mynda respectively.

 

As a newbie to the world of Power Query and Data Models just want to get as much info and expert guidance as possible! 🙂

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1845
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
9
June 27, 2019 - 2:47 pm
sp_Permalink sp_Print

Hi,

Well, if you don't have PP, not many choices left.

A SUMIF in Power Query can be replicated with Group By, from Transform tab, you can choose which columns will remain and which column will be aggregated (you can also choose the aggregation: SUM, COUNT, and so on).

Avatar
tan yan
Member
Members
Level 0
Forum Posts: 17
Member Since:
April 18, 2020
sp_UserOfflineSmall Offline
10
April 18, 2020 - 1:29 am
sp_Permalink sp_Print sp_EditHistory

agree with this method. However, notice everytime after i use the Group by function, all other columns that i wish to maintain will be auto dissappear when i upload into excel.....is there a way i could still maintain the other columns after using "Group by" function? 

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1845
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
11
April 18, 2020 - 3:13 am
sp_Permalink sp_Print

Hi,

You can copy the Group By step formula, remove that step and use instead a new column with the Group By formula.

This way, you can expand only the result, but it will be repeated on all identical rows.

Avatar
tan yan
Member
Members
Level 0
Forum Posts: 17
Member Since:
April 18, 2020
sp_UserOfflineSmall Offline
12
April 28, 2020 - 12:56 am
sp_Permalink sp_Print sp_EditHistory

Yes, you are right. lot of identical rows appeared after testing...not sure if any other better option is available here instead? 

anyway, thanks for your suggestion...great to learn one more skill from you 🙂

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1845
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
13
April 28, 2020 - 2:16 pm
sp_Permalink sp_Print

You can add another step, to remove duplicate rows, this way you will still have all columns.

The key columns that needs to be selected to remove duplicates should be the same used in grouping.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Sameh Alami, Donnacha Holly, Ineke Smit
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 880
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 237
Jessica Stewart: 219
A.Maurizio: 213
Aye Mu: 201
Hans Hallebeek: 188
Newest Members:
JUDY MLL
Scot Bailey
Kate Dyka
Kwaje Alfred Mogga
thong nguyen
Appiagyei Kofi Frimpong
Hilary Burchfield
Richie Wright
Adel Kock
Barbara Murray
Forum Stats:
Groups: 3
Forums: 24
Topics: 6548
Posts: 28672

 

Member Stats:
Guest Posters: 49
Members: 32834
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.