Log In
Register
Home
April 25, 2017
Offline
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
See Answer
July 16, 2010
Offline
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
October 17, 2015
Offline
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
Answers Post
April 25, 2017
Offline
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. 🙂
July 16, 2010
Offline
Great solution, Blanka! I've never noticed the All Rows option in the group dialog. I learnt something new too 🙂
Mynda
New Member
June 24, 2019
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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.
New Member
June 24, 2019
Offline
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! 🙂
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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).
April 18, 2020
Offline
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?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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.
April 18, 2020
Offline
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 🙂
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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.
1 Guest(s)
