group by

Hello. I am new to using Power Query and have gotten stuck on something and am looking for some help. In a data set I would like to categorize the values in one of the fields using some kind of If/Then/And/Or logic. I can figure out the logic if I was using an Excel formula, but I am...

I work as a sales data analyst for a clothing manufacturer. And I often make sales reports that are structured by Brand-season-Category. I constantly have to manually select rows and group data. Maybe there is a way to write a macro to do this automatically ? A macro with constant strings will...

Hello all, I'm currently working on a query that will eventually feed into a chart. The original table was too large and exceeded the chart's maximum 255 data series so I wanted to rewrite the query to group the rows by multiple columns with constant names. The issue is that the header names of...

I used the above code shared by @CalcSux78 to group product names in a column with similar words. I was wondering on how it the reordering can also be expanded to include other columns in the sheet. Right now it sorts the selected column, but data in other columns remains the same so there is a...

Hi Experts, Project: I am working on Power BI project where I am fetching data from many JIRA projects. The only problem here is one field which is not used in a harmonized way for across all JIRA projects and i.e. Release Information. For all JIRA projects in my report, I derived that...

I am going over tutorial from Microsoft website "Combine Data from multiple data sources". The issue is that on Step 6 "Group rows by ProductID and Year". In that step, I chose two columns and clicked on "Group By" and then got transparent windows and Excel stuck. This happened two times and...

Hi all I am trying to analyse the following dataset and get the total amount of first 5 items of each group(2 grps in total). I tried to use sumifs but since there are too many unique items in my data, selecting and filtering makes the table too complicated. Thanks for the help in advance...

Hi, I have data in a sheet with something like this: <tbody> A B C D File1 Abs 145 YuoJ 002 RFG 234 3op 009 File2 UYR 234 yyt 3nn POC 778 www 334 File3 WEF 778 O93 R67 TRE 778 87K 004 </tbody> I would like to colorize with the same background color all rows that...

I've had a few instances where I am grouping by 4 or 5 columns but then need to sum maybe 12 to 15 columns. Is there a way to accomplish this without having to enter the sum fields one at a time thru all sum columns?

Hi All, I have a module written to pull data from one sheet (Raw Data) and into another (Opportunities). But I am having trouble with the last piece. I THINK I need to group by Column B in Raw Data - but I still want to sum by Column C in Raw Data. Not sure if that is correct, but I was able...

Hi, I'm looking for a formula (that can be used in an excel Table) that allows me to add a ranking on row level that is calculated on the total sales per category. This means that rows from the same category should all have the same ranking. An example of the Table and the desired result...

Below is a portion of an Access query result I am working with: Dig ID Address Date Purpose Equipment used 2 131 Frontier 08/08/2015 Main Repair Backhoe 2...

Hello, I'm having a problem that is throughout my database, but I will limit it to one example in this post. I'm running a query on records with ID '100' which returns two pieces of data both with EARNED_PREMIUM_USD ($360.01). Logically you could expect the sum of these to be ($720.02), but...

Hi all, I have a quick question about performing "group by" in excel. I currently have to 2 columns named, "name"' and "number" as shown below. Name Number Balance 2000 Balance 300 Balance 200 Sales Deduction 20 Sales Deduction 30 ROI 20 ROI 10 Balance 29 Sales Deduction 1000 ROI 90 Sales...

Good evening all. I was previously looking (see “Grouping sheets from multiple workbooks”) for a solution that took a folder full of single-sheet workbooks relating to team reports (eg “Team Alpha Q1”, “Team Bravo Q1”, “Team Alpha Q2”, “Team Bravo Q2”) and consolidating these into a number of...

Hey hey hey This has had me flustered for nearly the whole day and I'm at my wits' end. A table has multiple 'titleids' (just a number column), with some different dates and some the same (so you could see the same titleid for twice on the 1st Jan, once on the 2nd Jan, once on the 5th Jan) I...

Can someone please help me with this sql statement for Excel MS Query? I tried my best and couldn't get it to work. All I want is two columns - Sum of DTOTAL grouped by HENTDT (order date). I prefer to have date range parameters picked up from two cells in Excel, but not necessarily; I could...

Hi, I'm trying to connect an Excel data table to the results of an Access query. This works fine using the "From Access" on the Data Ribbon. The issue I have is with a query that is a 'group by' type. It isn't possible to select this query from the "From Access" table/view selection. If I...

Hi all, I have "x" categories of different sizes and would like to create "y" teams with the same proportion of each category in it. Also I need the teams to be of equal size plus or minus maximum one individual. For instance, let's stay I have 3 categories: Bachelor's, 8 students; Master's...

I am trying to view the difference in Sales from one period to the next in an Excel PivotTable. Transaction dates are grouped by Years/Quarters. Years and Quarters are Row Labels in the PivotTable. The PivotTable Values are the Sum of Sales. Values are shown as the Difference From, with the...