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
*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
groupRows
Summary:
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
groupTable
Pete