I noticed Will Sutton started making videos based off of the Preppin’ Data data prep challenges. He has videos on his channel on how to complete the challenges using SQL, R, and Python. I highly recommend those videos for people upskilling in any of those languages, Will is a natural presenter and so good at explaining complicated topics.
As I was watching his demo, I thought to myself, “Could I do this with Power Query?” I decided to give it a shot.
My trial balloon was Week One for 2022. I probably should have started with 2023, but it was the first one in the list.
This challenge ended up being great because it showcased some features in Power Query some may not know about. I made a ~15 min video, hope this helps:
Here is my M code:
let
Source = Excel.CurrentWorkbook(){[Name="WeekOne"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"pupil first name", type text}, {"pupil last name", type text}, {"gender", type text}, {"Date of Birth", type date}, {"Parental Contact Name_1", type text}, {"Parental Contact Name_2", type text}, {"Preferred Contact Employer", type text}, {"Parental Contact", Int64.Type}}),
// Added a merge column for the pupil, delimiter is comma with a space
#"Merge Pupil Last, First" = Table.AddColumn(#"Changed Type", "Pupil's Name", each Text.Combine({[pupil last name], [pupil first name]}, ", "), type text),
// Used an if/then to merge based on parental contact column
#"Choose Parent" = Table.AddColumn(
#"Merge Pupil Last, First",
"Parental Contact Full Name",
each if [Parental Contact]=1 then [pupil last name] & ", " & [Parental Contact Name_1]
else [pupil last name] & ", " & [Parental Contact Name_2],
type text
),
// Used column from examples to get the email
#"Parent Email Col From Examples" = Table.AddColumn(
#"Choose Parent",
"Parental Contact Email Address",
each let splitpupillastname =
List.Reverse(
Splitter.SplitTextByDelimiter(
" ",
QuoteStyle.None
)([pupil last name])
),
splitParentalContactFullName =
Splitter.SplitTextByDelimiter(
", ",
QuoteStyle.None
)([Parental Contact Full Name])
in Text.Combine(
{
Text.Lower(splitpupillastname{1}?),
Text.Lower(splitpupillastname{0}?),
".",
Text.Lower(splitParentalContactFullName{1}?),
"@",
Text.Lower([Preferred Contact Employer]), ".com"}
),
type text
),
// Got minimum birth date and translated to integer
#"Min Date of Birth" = Int64.From(List.Min(#"Parent Email Col From Examples"[Date of Birth])),
// Got maximum birth date and translated to integer
#"Max Date of Birth" = Int64.From(List.Max(#"Parent Email Col From Examples"[Date of Birth])),
// Made a date list from min to max with the integers
#"Date List" = {#"Min Date of Birth" .. #"Max Date of Birth"},
// Converted the date list to a date table
#"Converted to Table" = Table.FromList(#"Date List", Splitter.SplitByNothing(), {"Dates"}, null, ExtraValues.Error),
// Changed the integer dates to actual dates
#"Changed Date Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Dates", type date}}),
// On the date table, calculated the academic years
#"Get Academic Year" = Table.AddColumn(#"Changed Date Type", "Academic Year",
each if [Dates] > #date(2014, 9, 1) then 1
else if [Dates] > #date(2013, 9, 1) then 2
else if [Dates] > #date(2012, 9, 1) then 3
else if [Dates] > #date(2011, 9, 1) then 4
else if [Dates] > #date(2010, 9, 1) then 5
else "N/A", Int64.Type),
// Joined the date table back to previous step
#"Join Academic Year" = Table.Join(#"Parent Email Col From Examples", "Date of Birth",#"Get Academic Year", "Dates"),
// Choose what I need
#"Choose Columns" = Table.SelectColumns(#"Join Academic Year",{"Pupil's Name", "Parental Contact Full Name", "Parental Contact Email Address", "Academic Year"}),
// Reordered at the end
#"Reordered Columns" = Table.ReorderColumns(#"Choose Columns",{"Academic Year", "Pupil's Name", "Parental Contact Full Name", "Parental Contact Email Address"})
in
#"Reordered Columns"
