•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Filter and Group by using Power Query or Excel formula?
Our take
When calculating average annualized salary across your company, you have two approaches: using Power Query or Excel formulas. Power Query allows for efficient data manipulation, letting you filter out department B and group by employee number seamlessly. Alternatively, you can utilize Excel formulas to achieve the same results, applying filters and calculations directly within your spreadsheet. Each method has its merits, but Power Query may offer a more streamlined process for handling complex datasets.
Let’s say I want to calculate average annualized salary over my company.
My data set is
Department | Employee Number | Salary Grade | Annualized Salary | FTE %
Someone can work 60% on a level 1 salary for department A and 40% on a level 2 salary for department B. In the dataset this person has 2 rows.
There are two metrics I want to calculate:
- average annualized salary across the whole company
- average annualized salary across the whole company excluding department B
For 1 - that employee’s annualized salary need to be recalculated as 60% * lvl1 + 40% lvl 2 to get one row per employee
For 2 - I filter out all department B salaries so that employee’s annualized salary is simply their lvl 1 salary
Question: Should I:
Create two tables in power query, filter out department B in one table using Power Query, group by employee number for both tables in power query, output both queries as tables in excel, create 2 pivot tables to calculate averages
OR
- Use excel formulas to do everything: filter and group by and calculate 2 averages
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How do I Maximize File EfficiencyI work with data sets that I typically look at forecast by year. Currently when I look at 2026 and 2027 it is rougly 1.4M lines of data. I have to put these in two separate data pulls and tables. Then I have six different customers included in this data. so I have to create 6 tabs with six diffrent pivot tables for them to look at. This has created a massive file that lags just to open, save or close so I really have two questions and am open to suggestions. Would it be better to store the data in one worksheet and then link a second worksheet that just has the pivot tables and separated look? If so how would I creat that link? Can you explain to me like I am 5 how I would use power query to combine the 26 and 27 table so that they could be in the same pivot table? Every column in both are identical. submitted by /u/dcal69 [link] [comments]
- Showing a row where no value existsI have a list of 5 countries we sell products to. And we split it into 3 product categories: internet, consulting services, phone. I have used power query to and loaded the connection into the data model, but not as a table within a worksheet. These are my total sales amount pivot tables: Filter: USA phone | 500 mill Consulting | 20 mill Internet | 30 mill Filter: New Zealand Phone | 5 mill Internet | 20 mill Problem: I need New Zealand to show Consulting and a sales amount of 0. There is no “consulting” row in the source data itself for New Zealand so “show zero values” in pivot table settings doesn’t work I figure I’ll need to create my own tables listing: Phone, Consulting and Internet as row headings. But how should I get my sales sums? I’m guessing doing xlookups using my pivot table is the data source is not best practice Do I scrap my pivot tables and load the power query as a table for sumif? submitted by /u/Interesting-System [link] [comments]
- Power query for a large datasetMy company uses a horrible format for its daily production sheets, but the data can be pulled through power query. I want to build a reporting tool for looking at any major trends that are currently missed. Ideally looking at part efficiency by machine type and some other descriptive data too like efficiency by shift manger etc. My problem is that even after cutting unnecessary columns and filtering unnecessary rows, it takes forever to load anything. ChatGPT isn’t all that helpful, I’d like some expert advice please! For info, rough number of rows of data is about 50,000 per year. I want to cover at least the last three years. Sheets are all saved into a folder by month, within a folder by year. submitted by /u/CanJesusSwimOnLand [link] [comments]
- I’ve been using Excel more lately and I’m trying to understand some of its more advanced features without making everything overly complicatedWhen working with data that has multiple conditions or needs to update automatically, what are the most efficient functions or tools to use? for example, is it better to rely on formulas like XLOOKUP and FILTER, or are there built in tools that handle this more cleanly? Also, how does excel handle performance when formulas start getting longer or more complex? Is there a point where using too many formulas slows things down significantly? What are the best built-in features in Excel for handling complex data in a simple way? submitted by /u/icepix [link] [comments]