﻿// 完成ブック.xlsx と同じM式です。説明用の架空例。
// 空のブックで［データ］→［データの取得］→［その他のデータソースから］→［空のクエリ］を作り、
// 各クエリの名前を「shared」の後ろの名前に変え、= の右側（let 〜 in の末尾まで）を詳細エディターへ貼り付けます。

section Section1;

shared 入力フォルダー = "C:\月次CSV\01_raw" meta [IsParameterQuery = true, Type = "Text", IsParameterQueryRequired = true];

shared 受領ファイル = let
    フォルダー = if Text.EndsWith(入力フォルダー, "\") or Text.EndsWith(入力フォルダー, "/") then 入力フォルダー else 入力フォルダー & (if Text.Contains(入力フォルダー, "/") then "/" else "\"),
    ソース = Folder.Files(フォルダー),
    直下のCSV = Table.SelectRows(ソース, each Text.Lower([Extension]) = ".csv" and Text.Lower([Folder Path]) = Text.Lower(フォルダー)),
    名前の月 = Table.AddColumn(直下のCSV, "ファイルの月", each
        if Text.StartsWith([Name], "sales_") and Text.Length([Name]) = 16 and Text.Lower(Text.End([Name], 4)) = ".csv"
            and List.AllTrue(List.Transform(Text.ToList(Text.Middle([Name], 6, 6)), each Text.Contains("0123456789", _)))
        then Text.Middle([Name], 6, 4) & "-" & Text.Middle([Name], 10, 2)
        else null, type nullable text),
    判定 = Table.AddColumn(名前の月, "判定", each if [ファイルの月] = null then "対象外" else "採用", type text),
    理由 = Table.AddColumn(判定, "理由", each if [ファイルの月] = null then "ファイル名が sales_年月.csv の形ではありません。コピーや別の版でないか、出力元に確かめます" else "", type text),
    列 = Table.SelectColumns(理由, {"Name", "ファイルの月", "判定", "理由", "Content"}),
    名前変更 = Table.RenameColumns(列, {{"Name", "ファイル名"}}),
    並べ替え = Table.Sort(名前変更, {{"ファイル名", Order.Ascending}})
in
    並べ替え;

shared ファイル一覧 = let
    ソース = Table.RemoveColumns(受領ファイル, {"Content"})
in
    ソース;

shared 明細 = let
    採用 = Table.SelectRows(受領ファイル, each [判定] = "採用"),
    読込み = Table.AddColumn(採用, "表", each
        let
            CSV = Csv.Document([Content], [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
            見出し = Table.PromoteHeaders(CSV, [PromoteAllScalars = true]),
            見出しの整理 = Table.TransformColumnNames(見出し, each Text.Trim(Text.Replace(_, Character.FromNumber(65279), ""))),
            列名統一 = Table.RenameColumns(見出しの整理, {{"商品CD", "商品コード"}}, MissingField.Ignore),
            必須列 = Table.SelectColumns(列名統一, {"対象月", "取引ID", "商品コード", "日付", "金額"}, MissingField.UseNull)
        in
            必須列),
    展開 = Table.ExpandTableColumn(Table.SelectColumns(読込み, {"ファイル名", "ファイルの月", "表"}), "表", {"対象月", "取引ID", "商品コード", "日付", "金額"}),
    文字列 = Table.TransformColumnTypes(展開, {{"対象月", type text}, {"取引ID", type text}, {"商品コード", type text}, {"日付", type text}, {"金額", type text}}),
    日付の値 = Table.AddColumn(文字列, "日付の値", each
        let t = if [日付] = null then "" else Text.Trim([日付])
        in try (if Text.Length(t) = 10 and Text.Middle(t, 4, 1) = "-" and Text.Middle(t, 7, 1) = "-"
                then #date(Number.From(Text.Start(t, 4)), Number.From(Text.Middle(t, 5, 2)), Number.From(Text.End(t, 2)))
                else null) otherwise null, type nullable date),
    金額の値 = Table.AddColumn(日付の値, "金額の値", each
        try (let n = Number.FromText(Text.Trim([金額]), "en-US") in if n = Number.Round(n, 0) then n else null) otherwise null, Int64.Type),
    同じID = Table.Group(金額の値, {"取引ID"}, {{"同じIDの行数", each Table.RowCount(_), Int64.Type}}),
    結合 = Table.NestedJoin(金額の値, {"取引ID"}, 同じID, {"取引ID"}, "同じID", JoinKind.LeftOuter),
    重複数 = Table.ExpandTableColumn(結合, "同じID", {"同じIDの行数"}),
    理由 = Table.AddColumn(重複数, "確認が必要な理由", each Text.Combine(List.RemoveNulls({
        if [取引ID] = null or Text.Trim([取引ID]) = "" then "取引IDが空欄" else null,
        if [商品コード] = null or Text.Trim([商品コード]) = "" then "商品コードが空欄" else null,
        if [日付の値] = null then "日付が読めない" else null,
        if [金額の値] = null then "金額が読めない" else null,
        if [対象月] <> [ファイルの月] then "対象月がファイル名と違う" else null,
        if [日付の値] <> null and Date.ToText([日付の値], "yyyy-MM") <> [対象月] then "日付の月が対象月と違う" else null,
        if [同じIDの行数] <> null and [同じIDの行数] > 1 then "取引IDが重複" else null
    }), "／"), type text),
    並べ替え = Table.Sort(理由, {{"ファイル名", Order.Ascending}, {"取引ID", Order.Ascending}})
in
    並べ替え;

shared 正常明細 = let
    正常 = Table.SelectRows(明細, each [確認が必要な理由] = ""),
    列 = Table.SelectColumns(正常, {"ファイル名", "対象月", "取引ID", "商品コード", "日付の値", "金額の値"}),
    名前変更 = Table.RenameColumns(列, {{"日付の値", "日付"}, {"金額の値", "金額"}}),
    型 = Table.TransformColumnTypes(名前変更, {{"日付", type date}, {"金額", Int64.Type}})
in
    型;

shared 確認が必要な行 = let
    確認 = Table.SelectRows(明細, each [確認が必要な理由] <> ""),
    列 = Table.SelectColumns(確認, {"ファイル名", "対象月", "取引ID", "商品コード", "日付", "金額", "確認が必要な理由"})
in
    列;

shared 月別集計 = let
    まとめ = Table.Group(明細, {"ファイル名", "ファイルの月"}, {
        {"取込み行数", each Table.RowCount(_), Int64.Type},
        {"正常行数", each Table.RowCount(Table.SelectRows(_, each [確認が必要な理由] = "")), Int64.Type},
        {"正常金額", each List.Sum({0} & Table.SelectRows(_, each [確認が必要な理由] = "")[金額の値]), Int64.Type},
        {"確認行数", each Table.RowCount(Table.SelectRows(_, each [確認が必要な理由] <> "")), Int64.Type},
        {"確認行の金額", each List.Sum({0} & List.RemoveNulls(Table.SelectRows(_, each [確認が必要な理由] <> "")[金額の値])), Int64.Type}
    }),
    名前変更 = Table.RenameColumns(まとめ, {{"ファイルの月", "対象月"}}),
    並べ替え = Table.Sort(名前変更, {{"ファイル名", Order.Ascending}})
in
    並べ替え;
