// GL_All  (connection only)
// Reads the company-wide general ledger export and turns the report layout into one clean row per transaction.
let
    Settings     = Excel.CurrentWorkbook(){[Name = "tblSettings"]}[Content],
    Folder       = Settings{[Setting = "Data folder"]}[Value],
    FileName     = Settings{[Setting = "GL export file"]}[Value],
    Source       = Csv.Document(File.Contents(Folder & FileName), [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
    Promoted     = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),

    // The export prints each account once as a header row above its transactions.
    // A header row has no Doc Date, so copy its label into a new column and fill it down.
    AddAccount   = Table.AddColumn(Promoted, "GL Account", each if [Doc Date] = "" then [Posted Date] else null, type text),
    FillAccount  = Table.FillDown(AddAccount, {"GL Account"}),

    // Header rows and "Total for ..." rows have no Doc Date: drop them.
    Transactions = Table.SelectRows(FillAccount, each [Doc Date] <> ""),
    Typed        = Table.TransformColumnTypes(Transactions, {
                       {"Posted Date", type date}, {"Doc Date", type date}, {"Doc", type text}, {"Memo", type text},
                       {"Program ID", type text}, {"Program Name", type text}, {"Vendor", type text},
                       {"Employee", type text}, {"Debit", type number}, {"Credit", type number}}, "en-US"),
    // The running balance and the demo-data note are not part of a transaction.
    NoBalance    = Table.RemoveColumns(Typed, {"Balance", "Data Note"}, MissingField.Ignore),
    Account      = Table.AddColumn(NoBalance, "Account", each Text.BeforeDelimiter([GL Account], " - "), type text),
    Net          = Table.AddColumn(Account, "Net", each (if [Debit] = null then 0 else [Debit]) - (if [Credit] = null then 0 else [Credit]), type number),
    Month        = Table.AddColumn(Net, "Month", each Date.StartOfMonth([Doc Date]), type date),

    // Month-end accruals (ACR-) and their automatic reversals are not billable: the vendor's real bill is.
    // Text.Contains, not Text.StartsWith: export formats change, and in the demo data newer reversals read "Reversed - REV-...".
    Flagged      = Table.AddColumn(Month, "Billable", each not (Text.Contains([Doc], "ACR-") or Text.Contains([Doc], "REV-")), type logical)
in
    Flagged

// GL  (loads to sheet "GL")
// Billable transactions only.
let
    Source   = GL_All,
    Billable = Table.SelectRows(Source, each [Billable]),
    Result   = Table.RemoveColumns(Billable, {"Billable"})
in
    Result

// Labor  (loads to sheet "Labor")
// The central labor-cost export: one row per person, program and pay period.
let
    Settings = Excel.CurrentWorkbook(){[Name = "tblSettings"]}[Content],
    Folder   = Settings{[Setting = "Data folder"]}[Value],
    FileName = Settings{[Setting = "Labor export file"]}[Value],
    Source   = Csv.Document(File.Contents(Folder & FileName), [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    // Every row carries a "Data Note" marking the file as demo data; it is not part of the labor record.
    NoNote   = Table.RemoveColumns(Promoted, {"Data Note"}, MissingField.Ignore),
    Typed    = Table.TransformColumnTypes(NoNote, {
                   {"Employee ID", Int64.Type}, {"Employee Name", type text}, {"Program ID", type text},
                   {"Position", type text}, {"Pay Period End", type date}, {"Regular Hours", type number},
                   {"Overtime Hours", type number}, {"Pay Rate", type number}, {"Salary", type number},
                   {"Benefit Cost", type number}, {"Total Cost", type number}}, "en-US"),
    Month    = Table.AddColumn(Typed, "Month", each Date.StartOfMonth([Pay Period End]), type date)
in
    Month

// InvoiceLines  (loads to sheet "Lines")
// One row per program and budget line for the invoice month: budget, this period, contract to date, balance.
let
    InvoiceMonth = Date.StartOfMonth(List.Max(GL[Doc Date])),
    Programs   = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name = "tblPrograms"]}[Content],
                     {{"Program ID", type text}, {"Contract Start", type date}}),
    Budget     = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name = "tblBudget"]}[Content],
                     {{"Program ID", type text}, {"Sort", Int64.Type}, {"Invoice Line", type text}, {"Annual Budget", type number}}),
    AccountMap = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name = "tblAccountMap"]}[Content],
                     {{"Account", type text}, {"Invoice Line", type text}, {"Program ID", type text}}),
    // A map row with a Program ID applies to that program only and wins over the shared row for the account.
    SharedMap  = Table.SelectRows(AccountMap, each [Program ID] = null or [Program ID] = ""),
    ProgramMap = Table.SelectRows(AccountMap, each [Program ID] <> null and [Program ID] <> ""),

    // Labor lines come from the labor export, split by position.
    LaborRows  = Table.RenameColumns(Table.SelectColumns(Labor, {"Program ID", "Month", "Position", "Total Cost"}),
                     {{"Position", "Invoice Line"}, {"Total Cost", "Amount"}}),

    // Everything else comes from the GL, mapped account -> invoice line (program rule first, then the shared rule).
    ByProgram  = Table.ExpandTableColumn(Table.NestedJoin(GL, {"Program ID", "Account"}, ProgramMap, {"Program ID", "Account"}, "P", JoinKind.LeftOuter),
                     "P", {"Invoice Line"}, {"Program Line"}),
    Mapped     = Table.ExpandTableColumn(Table.NestedJoin(ByProgram, {"Account"}, SharedMap, {"Account"}, "S", JoinKind.LeftOuter),
                     "S", {"Invoice Line"}, {"Shared Line"}),
    WithLine   = Table.AddColumn(Mapped, "Invoice Line", each if [Program Line] <> null then [Program Line] else [Shared Line], type text),
    Unmapped   = Table.ReplaceValue(WithLine, null, "(unmapped account)", Replacer.ReplaceValue, {"Invoice Line"}),
    GLRows     = Table.RenameColumns(
                     Table.SelectColumns(Table.SelectRows(Unmapped, each [Invoice Line] <> "(labor detail)"),
                         {"Program ID", "Month", "Invoice Line", "Net"}),
                     {{"Net", "Amount"}}),

    Activity   = Table.Combine({LaborRows, GLRows}),
    WithStart  = Table.ExpandTableColumn(
                     Table.NestedJoin(Activity, {"Program ID"}, Programs, {"Program ID"}, "P", JoinKind.Inner),
                     "P", {"Contract Start"}),
    InContract = Table.SelectRows(WithStart, each [Month] >= Date.StartOfMonth([Contract Start]) and [Month] <= InvoiceMonth),
    Totals     = Table.Group(InContract, {"Program ID", "Invoice Line"}, {
                     {"This Period", each List.Sum(Table.SelectRows(_, (r) => r[Month] = InvoiceMonth)[Amount]), type number},
                     {"Contract to Date", each List.Sum([Amount]), type number}}),

    // Full outer join: budget lines with no spend still print, and spend with no budget line still shows up.
    Joined     = Table.NestedJoin(Budget, {"Program ID", "Invoice Line"}, Totals, {"Program ID", "Invoice Line"}, "T", JoinKind.FullOuter),
    Expanded   = Table.ExpandTableColumn(Joined, "T", {"Program ID", "Invoice Line", "This Period", "Contract to Date"},
                     {"T.Program ID", "T.Invoice Line", "This Period", "Contract to Date"}),
    Program    = Table.AddColumn(Expanded, "Program", each if [Program ID] = null then [#"T.Program ID"] else [Program ID], type text),
    Line       = Table.AddColumn(Program, "Line", each if [Invoice Line] = null then [#"T.Invoice Line"] & " (no budget)" else [Invoice Line], type text),
    Clean      = Table.SelectColumns(Line, {"Program", "Sort", "Line", "Annual Budget", "This Period", "Contract to Date"}),
    Zeros      = Table.ReplaceValue(Clean, null, 0, Replacer.ReplaceValue, {"Annual Budget", "This Period", "Contract to Date"}),
    SortFix    = Table.ReplaceValue(Zeros, null, 99, Replacer.ReplaceValue, {"Sort"}),
    Balance    = Table.AddColumn(SortFix, "Balance", each [Annual Budget] - [Contract to Date], type number),
    Sorted     = Table.Sort(Balance, {{"Program", Order.Ascending}, {"Sort", Order.Ascending}, {"Line", Order.Ascending}}),

    // Number each program's lines 1..n so the invoice template can print them in order, off-budget lines last.
    Numbered   = Table.Combine(Table.Group(Sorted, {"Program"}, {{"Rows", each Table.AddIndexColumn(_, "Slot", 1, 1, Int64.Type)}})[Rows]),
    Key        = Table.AddColumn(Numbered, "Key", each [Program] & "|" & Text.From([Slot]), type text),
    Stamped    = Table.AddColumn(Key, "Invoice Month", each InvoiceMonth, type date),
    Result     = Table.RenameColumns(Stamped, {{"Program", "Program ID"}, {"Line", "Invoice Line"}})
in
    Result

// Register  (loads to sheet "Register")
// One row per invoice. Invoice Total is what the invoice prints; the sheet compares it with billable GL
// (completeness) and compares the labor export with payroll posted in the GL (a second source).
let
    InvoiceMonth = Date.StartOfMonth(List.Max(GL[Doc Date])),
    // The invoice template prints a fixed number of line rows (name InvoiceSlots). Count the rows themselves,
    // not any value in them, so the check can never disagree with what prints.
    SlotCells = Excel.CurrentWorkbook(){[Name = "InvoiceSlots"]}[Content],
    Slots     = Table.RowCount(SlotCells) + (if Table.ColumnNames(SlotCells){0} = "Column1" then 0 else 1),
    Programs  = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name = "tblPrograms"]}[Content],
                    {{"Program ID", type text}, {"Program Name", type text}, {"Payer", type text}, {"Contract Start", type date}}),
    ThisMonth = (t as table) as table => Table.SelectRows(t, each [Month] = InvoiceMonth),

    Invoiced  = Table.Group(InvoiceLines, {"Program ID"}, {
                    {"Invoice Total", each List.Sum(Table.SelectRows(_, (r) => r[Slot] <= Slots)[This Period]), type number},
                    // spend on an account that is not in the map, or a line with no budget
                    {"Off-Budget Lines", each List.Count(List.Select([Sort], (s) => s = 99)), Int64.Type},
                    {"Lines Not Printed", each List.Count(List.Select([Slot], (s) => s > Slots)), Int64.Type}}),
    GLBill    = Table.Group(ThisMonth(GL), {"Program ID"}, {{"GL Billable", each List.Sum([Net]), type number}}),
    GLPay     = Table.Group(Table.SelectRows(ThisMonth(GL), each [Account] = "5100" or [Account] = "5200"),
                    {"Program ID"}, {{"GL Payroll", each List.Sum([Net]), type number}}),
    LaborTot  = Table.Group(ThisMonth(Labor), {"Program ID"}, {{"Labor Detail", each List.Sum([Total Cost]), type number}}),
    Excluded  = Table.Group(Table.SelectRows(ThisMonth(GL_All), each not [Billable]),
                    {"Program ID"}, {{"Accruals Excluded", each List.Sum([Net]), type number}}),

    J1 = Table.ExpandTableColumn(Table.NestedJoin(Programs, {"Program ID"}, Invoiced, {"Program ID"}, "a", JoinKind.LeftOuter), "a", {"Invoice Total", "Off-Budget Lines", "Lines Not Printed"}),
    J2 = Table.ExpandTableColumn(Table.NestedJoin(J1, {"Program ID"}, GLBill, {"Program ID"}, "b", JoinKind.LeftOuter), "b", {"GL Billable"}),
    J3 = Table.ExpandTableColumn(Table.NestedJoin(J2, {"Program ID"}, GLPay, {"Program ID"}, "c", JoinKind.LeftOuter), "c", {"GL Payroll"}),
    J4 = Table.ExpandTableColumn(Table.NestedJoin(J3, {"Program ID"}, LaborTot, {"Program ID"}, "d", JoinKind.LeftOuter), "d", {"Labor Detail"}),
    J5 = Table.ExpandTableColumn(Table.NestedJoin(J4, {"Program ID"}, Excluded, {"Program ID"}, "e", JoinKind.LeftOuter), "e", {"Accruals Excluded"}),
    Zeros = Table.ReplaceValue(J5, null, 0, Replacer.ReplaceValue,
                {"Invoice Total", "GL Billable", "GL Payroll", "Labor Detail", "Accruals Excluded", "Off-Budget Lines", "Lines Not Printed"}),

    Request   = Table.AddColumn(Zeros, "Request No", each
                    (Date.Year(InvoiceMonth) - Date.Year([Contract Start])) * 12 + Date.Month(InvoiceMonth) - Date.Month([Contract Start]) + 1, Int64.Type),
    InvoiceNo = Table.AddColumn(Request, "Invoice No", each [Program ID] & "-" & Date.ToText(InvoiceMonth, "yyMM"), type text),
    PStart    = Table.AddColumn(InvoiceNo, "Period Start", each InvoiceMonth, type date),
    PEnd      = Table.AddColumn(PStart, "Period End", each Date.EndOfMonth(InvoiceMonth), type date),
    Sorted    = Table.Sort(PEnd, {{"Program ID", Order.Ascending}}),
    Result    = Table.SelectColumns(Sorted, {"Invoice No", "Program ID", "Program Name", "Payer", "Request No", "Period Start",
                    "Period End", "Invoice Total", "GL Billable", "Labor Detail", "GL Payroll", "Accruals Excluded", "Off-Budget Lines",
                    "Lines Not Printed"})
in
    Result