Add certain columns with powerquery?

[Vedes]

Hi

Ive got a 3000+ row report that Im trying to consolidate.
Right now our agents have a row per event, and I would like to make powerquery add up the events (quantity/column I in the example) if 2 conditions are met:
Condition 1: has to be the same agent ID
Condition 2: has to be the same absence ID

Ive attached an example of what it looks like now, after transforming the data in powerquery, and what I would like it to look like at the end. The end goal is a 2nd sheet where I use xlookup's to find the specific agents total paid sick days, vacation hours or other absence codes we use, according to this report and another report, to see if theres a difference.

Thanks in advance

[jdelano]

How does the initial 3000+ rows get into Excel? Are you importing a delimited text file?

There are a few ways you can do this,
If the data is imported directly into Excel via External data link to a table, then using an Excel Pivot table. might work for you.
https://support.microsoft.com/en-us/...c-c84a5a340725

See the attached, I'm not super experienced with them (as you can see by the one, I created) you should be able to get the pivot table formatted how you want it.

If the data is manually imported via a text file, it is possible to use an SQL type query to pull the data in using sum and grouping by reading the external data source without the need for intermediate steps in Excel.