// Aggregate and condense raw pull sheets from directory .\Raw Pull\
// Does not pull CommanderProfileReport at this time.
let

// Get Raw Files
    LocalFilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
    RawFileNames = {"UMR", "HearingReadinessReport", "DentalReadinessReport", "Pha_Report", "VisionReadinessReport", "HIVReadinessSummaryReport", "UMR Data"},
    RawFilePaths = List.Transform(RawFileNames, (_) => LocalFilePath & "\Raw Pull\" & _ & ".xlsx"),

// Promote headers after skipping 1 row because each MEDPROS sheet comes with pull metadata in first row
    RawSheets = List.Transform(RawFilePaths, (_) => Table.PromoteHeaders(Table.Skip(Excel.Workbook(File.Contents(_)){[Item="Sheet1", Kind="Sheet"]}[Data]))),

// In each table, remove all rows that have null in last column (bottom summary section) and only leave Soldier rows
    FilterSheets = List.Transform(RawSheets , each Table.SelectRows(_, (row) => List.Last(Record.ToList(row)) <> null)),

// Make sure each table has names padded to 27 chars (Max length for MEDPROS), because Dental doesn't play nicely
    PadNames = List.Transform(FilterSheets, (_) => Table.ReplaceValue(_, each [Name], each Text.PadEnd([Name], 27), Replacer.ReplaceValue, {"Name"})),

// Use random unique column names in each sheet to determine what MEDPROS pull it is
// Join all tables, using determined table type to name the column appropriately
    TableContains = (tab, word) => List.Contains(Table.ColumnNames(tab), word),
    GetTableType = (tab) => if TableContains(tab, "Panx") then "Dental" else if TableContains(tab, "HRC") then "Hearing" else if TableContains(tab, "PHA Date") then "PHA Date" else if TableContains(tab, "HIV Test Current") then "HIV" else if TableContains(tab, "MR2-DNA") then "UMR"else if TableContains(tab, "Waiver") then "Vision" else "Unknown",
    JoinTables = List.Accumulate(List.RemoveFirstN(PadNames, 1), List.First(FilterSheets), (res, cur) => Table.NestedJoin(res, "Name", cur, "Name", GetTableType(cur))),

// Expand necessary columns from each table
    ExpandHearing = Table.ExpandTableColumn(JoinTables, "Hearing", {"Audiogram Date"}, {"Hearing Due Date"}),
    ExpandDental = Table.ExpandTableColumn(ExpandHearing, "Dental", {"Date of Next Exam"}, {"Dental Due Date"}),
    ExpandPHA = Table.ExpandTableColumn(ExpandDental, "PHA Date", {"Next PHA Date"}, {"PHA Due Date"}),
    ExpandVision = Table.ExpandTableColumn(ExpandPHA, "Vision", {"Date of Vision Screen", "2PG", "1MI", "MCEP", "MCEPI"}, {"Vision Due Date", "2x Glasses", "1x Mask Ins", "Eye-Pro", "Eye-Pro Inserts"}),
    ExpandHIV = Table.ExpandTableColumn(ExpandVision, "HIV", {"Next Test Date"}, {"HIV Due Date"}),
    ExpandImms = Table.ExpandTableColumn(ExpandHIV, "UMR", {"MR2-IMM"}, {"ImmsList"}),

// Split MR2-IMM column from UMR Data pull into individual Imms columns for Korea profile.
// Tdap is initially changed to Dtap so that using Text.Contains has something unique to grab onto.
    PrepTdap = Table.ReplaceValue(ExpandImms,"Tdap","Dtap",Replacer.ReplaceText,{"ImmsList"}),
    Imms = {{"Tyv", "Tyv"}, {"Ant", "Ant"}, {"JEV", "JEV"}, {"Flu", "Flu"}, {"HepA", "HepA"}, {"HepB", "HepB"}, {"Td", "TDA"}, {"Dtap", "TDP"}, {"Var", "Var"}, {"MMR", "MMR"}, {"IPV", "IPV"}, {"Spx", "Spx"}, {"CXX", "CXX"}},
    SplitImms = List.Accumulate(Imms, PrepTdap, (res, cur) => Table.AddColumn(res, cur{1}, each if Text.Contains([#"ImmsList"], cur{0}) then cur{1} else null)),

// Hearing and Vision pulls have date of exam, rather than due date, so adjust accordingly.
// Hearing = add 13 months, Vision = add 15 months according to MEDPROS website.
    AdjustHearing = Table.ReplaceValue(SplitImms, each [Hearing Due Date], each Date.AddMonths(Date.From([Hearing Due Date]), 13), Replacer.ReplaceValue, {"Hearing Due Date"}),
    AdjustVision = Table.ReplaceValue(AdjustHearing, each [Vision Due Date], each Date.AddMonths(Date.From([Vision Due Date]), 15), Replacer.ReplaceValue, {"Vision Due Date"}),

// Select Columns
    SelectCols = Table.SelectColumns(AdjustVision,{"Name", "Rank", "IMR Exclusion", "Age", "Sex", "MOS", "UIC", "MRC", "Deployable", "MR2-DNA", "MR2-HIV", "MR2-IMM", "MR2-IME", "MR2-Temp>=8<=30", "MR2-HRC4", "MR2-VRC4", "MR3-Temp>=31 (DL1)", "MR3-DRC3 (DL2)", "MR3-PRG (DL3)", "MR3-MAR2 (DL4)", "MR3-MEB (DL5)", "MR3-ND-PEB (DL6)", "MR3-PERM (DL7)", "MR4-DRC4", "MR4-PHA", "Hearing Due Date", "Dental Due Date", "PHA Due Date", "Vision Due Date", "2x Glasses", "1x Mask Ins", "Eye-Pro", "Eye-Pro Inserts", "HIV Due Date", "Tyv", "Ant", "JEV", "Flu", "HepA", "HepB", "TDA", "TDP", "Var", "MMR", "IPV", "Spx", "CXX"}),
// Reorder Columns
    ReorderCols = Table.ReorderColumns(SelectCols,{"Name", "Rank", "IMR Exclusion", "Age", "Sex", "MOS", "UIC", "MRC", "Deployable", "Hearing Due Date", "Dental Due Date", "PHA Due Date", "Vision Due Date", "2x Glasses", "1x Mask Ins", "Eye-Pro", "Eye-Pro Inserts", "HIV Due Date", "MR2-DNA", "MR2-HIV", "MR2-IMM", "MR2-IME", "MR2-Temp>=8<=30", "MR2-HRC4", "MR2-VRC4", "MR3-Temp>=31 (DL1)", "MR3-DRC3 (DL2)", "MR3-PRG (DL3)", "MR3-MAR2 (DL4)", "MR3-MEB (DL5)", "MR3-ND-PEB (DL6)", "MR3-PERM (DL7)", "MR4-DRC4", "MR4-PHA", "Tyv", "Ant", "JEV", "Flu", "HepA", "HepB", "TDA", "TDP", "Var", "MMR", "IPV", "Spx", "CXX"}),
// Sort Alphabetically
    SortAlphabetically = Table.Sort(ReorderCols,{{"Name", Order.Ascending}}),
// Change Types to Date for Date Cols
    SetDateTypes = Table.TransformColumnTypes(SortAlphabetically,{{"Hearing Due Date", type date}, {"Dental Due Date", type date}, {"PHA Due Date", type date}, {"Vision Due Date", type date}, {"HIV Due Date", type date}}),

// No promotable status indicators - To make column size more consistent in excel tables.
    HidePStatus = Table.TransformColumns(SetDateTypes, {{"Rank", each Text.Start(_, 3), type text}})
in
    HidePStatus