Hello,
thanks to everyone who participated! There was a difficulty in the workout which I didn’t hide in the small prints: “Be aware that more than one employee can have the max points per day or per category.” I also repeated the warning on LinkedIn, but probably I should have shouted it louder …
I’ll present both stages (intermediate and advanced) based on step-by-step grouping and merging, which will make sure that we find all max points per day and per category.
Both stages start off with two groupings: Mark the Date and Employee columns and choose on the Home tab Group By. Change New Column Name to Points and Operation to Sum and select as Column Points:
To get the Max Points per Date select the Date column and choose on the Home tab Group By. Change New Column Name to Max Points and Operation to Max and select as Column Points:
Join then the query against itself by pressing on the Home tab Merge Queries. For this you select the … (current) query and select both columns while pressing the CTRL key:
=> you need to change in the formula bar the first #“Grouped Max Date” to
#“Grouped Employees” and the first Max Points to
Points:
= Table.NestedJoin(
#“Grouped Employees”, {“Date”, “
Points”}, #“Grouped Max Date”, {“Date”, “Max Points”}, “Grouped Max Date”, JoinKind.LeftOuter)
Expand Max Points from the column “Grouped Max Date”:
Now you “simply” need to replace the values in Max Points with 20. You can do this with replacing any value on the Max Points column with 20 and then adjusting the code in the formula bar like this:
= Table.ReplaceValue(#“Expanded Grouped Max Date”, each if [Max Points] <> null then [Max Points] else “xyz”, 20,Replacer.ReplaceValue,{“Max Points”})
Or for an alternative technique mark the Max Points column and click on the Transform tab the Format button and select on the dropdown Add Prefix and then press any letter. That will create this basic syntax which we are going to change in the formula bar:
= Table.TransformColumns(#“Expanded Grouped Max Date”, {{“Max Points”, each “k” & Text.From(_, “de-DE”), type text}})
= Table.TransformColumns(#“Expanded Grouped Max Date”, {{“Max Points”, each if _ <> null then 20 else _}})
Change the step name to Transform and you’re done with the intermediate stage. Not too many steps and each step hopefully is not too difficult to understand and reproduce for you.
let
Source = Excel.CurrentWorkbook(){[Name="Bonus"]}[Content],
#"Grouped Employees" = Table.Group(Source, {"Date", "Employee"}, {{"Points", each List.Sum([Points]), type number}}),
#"Grouped Max Date" = Table.Group(#"Grouped Employees", {"Date"}, {{"Max Points", each List.Max([Points]), type number}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Employees", {"Date", "Points"}, #"Grouped Max Date", {"Date", "Max Points"}, "Grouped Max Date", JoinKind.LeftOuter),
#"Expanded Grouped Max Date" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Max Date", {"Max Points"}, {"Max Points"}),
Transformation = Table.TransformColumns(#"Expanded Grouped Max Date", {{"Max Points", each if _ <> null then 20 else _}})
in
Transformation
Second Stage incl. Cat Points:
After the identical first two steps as above, we want to build something similar for the max points of the category with the most points of the day. We use the same technique as before to be able to grab muliple first places, just this time we have to cover one level more.
But how can we refer to Source after the first two steps? Well, we can click on Source itself and then mark the Date and Employee columns and choose on the Home tab Group By. Change New Column Name to Points and Operation to Sum and select as Column Points like before but this time add two aggregations for Max and All Rows:
Now pull this newly created step to the last position of the steps and change the referenced step name to Source:
= Table.Group(
Source, {“Date”, “Category”}, {{“Points”, each List.Sum([Points]), type number}, {“Max”, each List.Max([Points]), type number}, {“All”, each _, type table [Date=datetime, Category=text, Employee=text, Points=number]}})
To get the max points of the categories per Date select the Date column and choose on the Home tab Group By. Change New Column Name to Max Points and Operation to Max and select as Column Points. In addition add one aggregation for All Rows to not lose any data:
Now we can twice expand the All column. The second time it will be automatically called All.1.
First time all columns except Date:
Second time all columns except Category and Date:
That gives us a lot of rows, but we only need the rows where Max Points correspond to Points and Max Points.1 equals Points.1. So let’s select any of the columns and select any of the values and then bring this to condition to the formula bar: each ([Max] = [Points] and [Max.1] = [Points.1])
Now comes the merging phase. Join the query against itself by pressing on the Home tab Merge Queries. For this you select the … (current) query and then bring in the same code as above:
= Table.NestedJoin(#“Grouped Employees”, {“Date”, “Points”}, #“Grouped Max Date”, {“Date”, “Max Points”}, “Max Points”, JoinKind.LeftOuter)
Join once more the query against itself by pressing on the Home tab Merge Queries. For this you select the … (current) query and select the Date and Employee columns while pressing the CTRL key. Then change in the formula bar the second step name and the column name:
= Table.NestedJoin(#“Merged Queries”, {“Date”, “Employee”}, #“Filtered Rows”, {“Date”, “Employee”}, “Cat Points”, JoinKind.LeftOuter)
We are almost there, if you want you can go ahead and expand both columns and replace / transform in several steps as above. Simply do this step-by-step if you want to practice the above techniques.
Or let’s enhance the transformation step, to directly check if the tables in the two columns are empty. In that case we write null and if they are not empty it means they contain the max values and we can allocate the Max Points respectively the Cat Points. Mark the Max Points column and click on the Transform tab the Format button and select on the dropdown Add Prefix and then press any letter. That will create this basic syntax which we are going to change in the formula bar:
= Table.TransformColumns(#“Merged Queries1”, {{“Max Points”, each “k” & Text.From(_, “de-DE”), type text}})
[Yes, that was right, mark only one column. The text for the second column you can copy and adapt.]
= Table.TransformColumns(#“Merged Queries1”, {{“Max Points”, each if Table.IsEmpty(_) then null else 20}, {“Cat Points”, each if Table.IsEmpty(_) then null else 10}})
Done! Basically the same technique, just a bit more complex. Means if you understood the first stage you can follow along also for the second stage.
let
Source = Excel.CurrentWorkbook(){[Name="Bonus"]}[Content],
#"Grouped Employees" = Table.Group(Source, {"Date", "Employee"}, {{"Points", each List.Sum([Points]), type number}}),
#"Grouped Max Date" = Table.Group(#"Grouped Employees", {"Date"}, {{"Max Points", each List.Max([Points]), type number}}),
#"Grouped Categories" = Table.Group(Source, {"Date", "Category"}, {{"Points", each List.Sum([Points]), type number}, {"Max", each List.Max([Points]), type number}, {"All", each _, type table [Date=datetime, Category=text, Employee=text, Points=number]}}),
#"Grouped Rows" = Table.Group(#"Grouped Categories", {"Date"}, {{"Max", each List.Max([Points]), type number}, {"All", each _, type table [Date=datetime, Category=text, Points=number, Max=number, All=table]}}),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Category", "Points", "Max", "All"}, {"Category", "Points", "Max.1", "All.1"}),
#"Expanded All.1" = Table.ExpandTableColumn(#"Expanded All", "All.1", {"Employee", "Points"}, {"Employee", "Points.1"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded All.1", each ([Max] = [Points] and [Max.1] = [Points.1])),
#"Merged Queries" = Table.NestedJoin(#"Grouped Employees", {"Date", "Points"}, #"Grouped Max Date", {"Date", "Max Points"}, "Max Points", JoinKind.LeftOuter),
#"Merged Queries1" = Table.NestedJoin(#"Merged Queries", {"Date", "Employee"}, #"Filtered Rows", {"Date", "Employee"}, "Cat Points", JoinKind.LeftOuter),
Transformation = Table.TransformColumns(#"Merged Queries1", {{"Max Points", each if Table.IsEmpty(_) then null else 20}, {"Cat Points", each if Table.IsEmpty(_) then null else 10}})
in
Transformation
Try both stages, but make sure to check also some of the neat and compact approaches above! => Hope you learn something!
Big thanks to everyone who has blured or hidden the details! I have taken this now out so that it’s easier to compare solutions.
