﻿let
    FolderPath = "C:\PQ配賦",
    // CSVはUTF-8。年月はYYYY-MMの文字列で統一する。
    ReadCsv = (FileName as text, Required as list, NumericColumn as text) as table =>
        let
            Path = Text.TrimEnd(FolderPath, {"\", "/"}) & "\" & FileName,
            Csv = Csv.Document(File.Contents(Path), [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
            Headers = Table.PromoteHeaders(Csv, [PromoteAllScalars=true]),
            Missing = List.Difference(Required, Table.ColumnNames(Headers)),
            Selected = if List.Count(Missing) > 0 then error Error.Record("列不足", FileName, Missing) else Table.SelectColumns(Headers, Required),
            Trimmed = Table.TransformColumns(Selected, List.Transform(Required, (c) => {c, each if _ = null then null else Text.Trim(Text.From(_)), type text})),
            Blanks = Table.SelectRows(Trimmed, (r) => List.AnyTrue(List.Transform(Required, (c) => let v = Record.Field(r, c) in v = null or v = ""))),
            Filled = if Table.RowCount(Blanks) > 0 then error Error.Record("入力漏れ", FileName & "：必須列に空欄があります。", Blanks) else Trimmed,
            Typed = Table.TransformColumnTypes(Filled, {{NumericColumn, type number}}, "ja-JP"),
            Errors = Table.SelectRowsWithErrors(Typed, {NumericColumn}),
            Numbers = if Table.RowCount(Errors) > 0 then error Error.Record("数値不正", FileName, Errors) else Typed,
            BadMonth = Table.SelectRows(Numbers, each let
                S = [年月], D = try Date.FromText(S & "-01", [Format="yyyy-MM-dd", Culture="en-US"])
                in if Text.Length(S) <> 7 then true else if D[HasError] then true else Date.ToText(D[Value], "yyyy-MM") <> S),
            Result = if Table.RowCount(BadMonth) > 0 then error Error.Record("年月不正", FileName & "：YYYY-MM形式にしてください。", BadMonth) else Numbers
        in Result,
    Actual = Table.Buffer(ReadCsv("製造間接費実績データ.csv", {"年月", "配賦元部門コード", "配賦元部門", "費目", "実績金額"}, "実績金額")),
    Master0 = Table.Buffer(ReadCsv("配賦マスタ.csv", {"年月", "配賦元部門コード", "配賦元部門", "配賦先製品コード", "配賦先製品", "配賦基準", "基準値"}, "基準値")),
    InvalidBase = Table.SelectRows(Master0, each [基準値] <= 0),
    Positive = if Table.RowCount(InvalidBase) > 0 then error Error.Record("配賦基準値不正", "基準値には0より大きい数値を指定してください。", InvalidBase) else Master0,
    Counts = Table.Group(Positive, {"年月", "配賦元部門コード", "配賦先製品コード"}, {{"件数", each Table.RowCount(_), Int64.Type}}),
    Duplicates = Table.SelectRows(Counts, each [件数] > 1),
    UniqueMaster = if Table.RowCount(Duplicates) > 0 then error Error.Record("マスタ重複", "同じ年月・部門・製品が重複しています。", Duplicates) else Positive,
    Bases = Table.Group(UniqueMaster, {"年月", "配賦元部門コード"}, {{"基準種類数", each List.Count(List.Distinct([配賦基準])), Int64.Type}}),
    MixedBases = Table.SelectRows(Bases, each [基準種類数] <> 1),
    Master = if Table.RowCount(MixedBases) > 0 then error Error.Record("基準混在", "同じ年月・部門内の配賦基準を統一してください。", MixedBases) else UniqueMaster,
    Totals = Table.Group(Master, {"年月", "配賦元部門コード"}, {{"基準値合計", each List.Sum([基準値]), type number}}),
    WithTotal = Table.NestedJoin(Master, {"年月", "配賦元部門コード"}, Totals, {"年月", "配賦元部門コード"}, "合計", JoinKind.LeftOuter),
    ExpandedTotal = Table.ExpandTableColumn(WithTotal, "合計", {"基準値合計"}),
    Rates = Table.AddColumn(ExpandedTotal, "配賦率", each [基準値] / [基準値合計], type number),
    // 未登録部門を落とさず検出してから、製品別に展開する。
    Joined = Table.NestedJoin(Actual, {"年月", "配賦元部門コード"}, Rates, {"年月", "配賦元部門コード"}, "配賦先", JoinKind.LeftOuter),
    Unmatched = Table.SelectRows(Joined, each Table.IsEmpty([配賦先])),
    Checked = if Table.RowCount(Unmatched) > 0 then error Error.Record("配賦先未登録", "実績の年月・部門に対応するマスタがありません。", Table.RemoveColumns(Unmatched, {"配賦先"})) else Joined,
    Expanded = Table.ExpandTableColumn(Checked, "配賦先", {"配賦先製品コード", "配賦先製品", "配賦基準", "基準値", "基準値合計", "配賦率"}),
    Allocated = Table.AddColumn(Expanded, "配賦額", each [実績金額] * [配賦率], type number),
    Selected = Table.SelectColumns(Allocated, {"年月", "配賦元部門コード", "配賦元部門", "費目", "実績金額", "配賦先製品コード", "配賦先製品", "配賦基準", "基準値", "基準値合計", "配賦率", "配賦額"}),
    // 配賦前は元の実績、配賦後は配賦額を集計。展開後の実績金額は合計しない。
    Before = Table.Group(Actual, {"年月", "配賦元部門コード"}, {{"配賦前", each List.Sum([実績金額]), type number}}),
    After = Table.Group(Selected, {"年月", "配賦元部門コード"}, {{"配賦後", each List.Sum([配賦額]), type number}}),
    AuditJoin = Table.NestedJoin(Before, {"年月", "配賦元部門コード"}, After, {"年月", "配賦元部門コード"}, "配賦後合計", JoinKind.LeftOuter),
    AuditExpanded = Table.ExpandTableColumn(AuditJoin, "配賦後合計", {"配賦後"}),
    AuditErrors = Table.SelectRows(AuditExpanded, each [配賦後] = null or Number.Abs([配賦後] - [配賦前]) > 0.000001),
    Audited = if Table.RowCount(AuditErrors) > 0 then error Error.Record("配賦前後不一致", "年月・部門別の金額を確認してください。", AuditErrors) else Selected,
    Result = Table.Sort(Audited, {{"年月", Order.Ascending}, {"配賦元部門コード", Order.Ascending}, {"費目", Order.Ascending}, {"配賦先製品コード", Order.Ascending}})
in
    Result
