Brian Julius Solution to Excel BI PQ 167 Challenge

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Vaccines = Table.AddIndexColumn( Table.Distinct( Table.SelectColumns( Table.SelectRows(Source, each [Notification Date] = null ), "Vaccine")), "CampNo", 1, 1),
    Names = Table.Sort( Table.RenameColumns(  Table.Distinct( Table.SelectColumns( Table.SelectRows(Source, each [Notification Date] <> null ), "Vaccine")), {"Vaccine", "Name"}), {"Name", Order.Ascending}),
    AddVaccine = Table.ExpandListColumn( Table.AddColumn(Vaccines, "Name", each Names[Name]), "Name"), 
    MatchTable = Table.TransformColumnTypes( Table.RenameColumns( Table.SelectRows( Table.FillDown( Table.ReplaceValue(Source,"Name",null,Replacer.ReplaceValue,{"Camp No"}), {"Camp No"}), each [Notification Date] <> null), {"Vaccine", "Name_"}), {"Camp No", Int64.Type}),
    Table.Join = Table.RemoveColumns( Table.Join(AddVaccine, {"Name", "CampNo"}, MatchTable, {"Name_", "Camp No"}, JoinKind.LeftOuter), {"Name_", "Camp No" }),
    AddNotified = Table.AddColumn(Table.Join, "Notified", each if [Notification Date] <> null then "Yes" else "No"),
    AddAdministered = Table.Sort( Table.RemoveColumns( Table.AddColumn(AddNotified, "Administered", each if [Notification Date] = null then "NA" else if [Administration Date] = null then "No" else "Yes"), {"Administration Date", "CampNo", "Notification Date"}), {{"Vaccine", Order.Descending}, {"Name", Order.Ascending}}),
    Group = Table.Group(AddAdministered, {"Vaccine"}, {{"All", each _}}),
    AddCampNo = Table.RemoveColumns( Table.AddColumn(Group, "CampNo", each Table.AddIndexColumn( [All], "Camp No", 1, 1)), "All"),
    Expand = Table.ExpandTableColumn(AddCampNo, "CampNo", {"Name", "Notified", "Administered", "Camp No"}, {"Name", "Notified", "Administered", "Camp No"}),
    Reorder = Table.ReorderColumns(Expand,{"Camp No", "Vaccine", "Name", "Notified", "Administered"})
in
    Reorder