Forum Discussion
COUNTIFS in Power Query (M)
OK, so I started from scratch just to make sure I was working through the issue correctly. I've attached the working PBIX at the bottom so I won't post each individual bit of code here.
- Your Days table I didn't do anything with directly, but is used later in a crossjoin.
- Your Events table I did some pre-prep on to get it looking like this:
- Your Employees table I used as the base for the final process, crossjoining the Dates table and merging the prepped Events table, before double-grouping to generate the daily stats.
Here's the output:
I think you'd got most of the way here already, so it's probably just the final groupings in the Employees table that are relevant. The first grouping just gets you to a single record per employee/date so you're not double-counting. The second grouping uses custom List.Counts to pick out the stats you're after.
Pete
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIE41gdCM8IiE3BPCeonBmcZwRX6YzQFwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Counter = _t, Days = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Days", Int64.Type}}),
DistEmployee = List.Count(List.Distinct(ChangedType[Employee])),
DistCounter = List.Count(List.Distinct(ChangedType[Counter])),
SumOfDays = List.Sum(ChangedType[Days]),
Result = Table.FromRecords({[NoEmployee = DistEmployee, NoCounter = DistCounter, SumOfDays = SumOfDays]})
in
Result*EDIT* Ignore this post, check out my reply to this post below - much simpler and maintains query folding
------------------------------------------------------------------------------------------
Cool, thanks for that.
Try this out:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWBsEzBLCcgywzOgsg6Q1ixAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Days = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Days", Int64.Type}}), groupEmployeeData = Table.Group(chgTypes, {"Employee"}, {{"data", each _, type table [Employee=nullable text, Counter=nullable number, Days=nullable number]}}), addNestedCounter = Table.TransformColumns(groupEmployeeData, {"data", each Table.AddIndexColumn(_, "Counter", 1, 1)}), expandDataCol = Table.ExpandTableColumn(addNestedCounter, "data", {"Days", "Counter"}, {"Days", "Counter"}), addGroupMe = Table.AddColumn(expandDataCol, "GroupMe", each "X"), groupRows = Table.Group(addGroupMe, {"GroupMe"}, {{"NoEmployee", each List.Count(List.Distinct(_[Employee])), Int64.Type}, {"NoCounter", each List.Max([Counter]), type number}, {"SumOfDays", each List.Sum([Days]), type number}}) in groupRowsSummary:
groupEmployeeData = Nest a table of data for each employee
addNestedCounter = Add an Index column to each employee table for the counter
expandDataCol = Reinstate nested tables/columns to main table
addGroupMe = Add a single-value column on which to group the whole table
groupRows = Group no single-value column using MAX of [Counter], SUM of [Days], and a custom group using List.Count(List.Distinct(... on [Employee]
It *should* be faster than a custom function, but too many variables to know for sure.
Pete
Ignore post above, you can do this with just a double-group, which will also maintain query folding:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWBsEzBLCcgywzOgsg6Q1ixAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Days = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Days", Int64.Type}}), groupEmployeeData = Table.Group(chgTypes, {"Employee"}, {{"NoCounter", each Table.RowCount(_), Int64.Type}, {"SumOfDays", each List.Sum([Days]), type nullable number}}), addGroupMe = Table.AddColumn(groupEmployeeData, "GroupMe", each "X"), groupTable = Table.Group(addGroupMe, {"GroupMe"}, {{"NoEmployee", each Table.RowCount(_), Int64.Type}, {"NoCounter", each List.Max([NoCounter]), type number}, {"SumOfDays", each List.Sum([SumOfDays]), type nullable number}}) in groupTablePete
Hello everyone,
Sorry for my lack of precise information.
You will find here an Excel file with what I want to do (the final query is the one named Employees).
In this very simple example, I have a list of employees who will be away during the week of the 26/12, and I need to know how many. In the events' list, there are some of them valued as 1/2 days, so when I merge everything I have multiple rows for a single employee, so I need a COUNTIFS() to bypass this issue and count them as 1 per day.
Thanks in advance!
OK, so a textbook example of an XY Problem.
On your shared file, which of the tables is our starting point i.e. which tables are the closest to your raw source?
On your Events table, what do the different [Types] mean? Do these signify multiple-day, single-day, and part-day absence?
Regarding the endpoint, I guess you want the date, number of employees (headcount), and number of days' worth of absence?
Pete
