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
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
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
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. 🙂
Great solution, Blanka! I've never noticed the All Rows option in the group dialog. I learnt something new too 🙂
Mynda
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
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.
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! 🙂
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).
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?
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.
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 🙂
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.
