Hello,
thanks to everyone who participated. And a special thanks to all the regular participants. Happy that you showed up again and again. Hope it helped elevating your skills!
Most (all?) of the workouts come from the real world and I love to demonstrate how to solve seemingly difficult problems with relatively easy steps. But you should always try to find alternatives. E.g. here @Luan has a nice, not too difficult logic wich you should bring in to your repertoire.
@AlexisOlson is right that one way to deal with this workkout is about joining, filtering and expanding. My beginner orientated approach brings the filtering behind the expansion.
Let’s start off in Table1 (PayDate and Employee) with selecting on the Home tab the Merge Queries dropdown and then Merg Queries as New:
Choose Table2 and for both queries select Effective date AND Employee:
That will create a new query with a new column Table2 at the end:
( Of course you could have merged also in Table1, but I think it is clearer to work in a separate query.)
This new column Table2 we expand, just taking the Effective Date:
Now we merge again, this time we take Merge Queries:
Again choose Table2 and this time select for both queries ONLY Employee:
We’ll get again a new column Table2 and we expand now all columns except Employee:
For Effective Date you will automatically get Effective Date**.1**
Click on column Effective Date and select the top right triangle. Select any of the values:
Change in the formula bar that value, with [Effective Date.1]:
We don’t need Effective Date.1 any more, so we click on it and press delete.
We are basically ready, but you might want to sort Pay Date and Emplyee in ascending:
Done!
let
Source = Table.NestedJoin(Table1, {"Pay Date", "Employee"}, Table2, {"Effective Date", "Employee"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table" = Table.ExpandTableColumn(Source, "Table2", {"Effective Date"}, {"Effective Date"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded Table", {"Employee"}, Table2, {"Employee"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Effective Date", "Home Dep", "Worked in Dep"}, {"Effective Date.1", "Home Dep", "Worked in Dep"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Table2", each ([Effective Date] = [Effective Date.1])),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Effective Date.1"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Pay Date", Order.Ascending}, {"Employee", Order.Ascending}})
in
#"Sorted Rows"
Probably you wouldn’t use this approach for really big data sets, but I like that it is really easy and mostly UI driven. That tweaking in the filtering is very useful and something you can apply again and again.
Try it out and make sure to check also the other approaches above! => Hope you learn something!
Big thanks to everyone who has blurred or hidden the details! It allows readers to develop their own ideas without being influenced - and it’s easy enough to check them afterwards.
Enterprise DNA has decided to temporarily pause the workout program. Let’s see what’s going to happen, in any case we have already a nice collection of 13 workouts: Hope you liked it so far and hope you learnt something. Thanks!
