なんでもエクセル(Nandemo Excel)

何でもエクセルでやってしまう そこのあなた。ようこそ

Power Query:「年」を入力したら「1月~12月」の月初日が並ぶようにする

まずは A1に「年」を数値で入力したら、その年の「1月~12月」がテーブルで読み込まれるようにする場合。これは簡単。

ひとまず A1を「年」と名前定義しておいて、空のクエリの詳細エディタに下記を入力。

let
    年 = Excel.CurrentWorkbook(){[Name="年"]}[Content]{0}[Column1],
    月リスト作成 = Table.FromList(
        {1..12}, Splitter.SplitByNothing(), {"日付"}
    ),
    年月作成 = Table.TransformColumns(
        月リスト作成, 
        {"日付", each Date.From(Number.ToText(年 + _/100, "0.00"))}
    )
in
    年月作成

これで「1月~12月」が表示される。

もし「4月~3月」にしたい場合はリストを少し変える。

let
    年 = Excel.CurrentWorkbook(){[Name="年"]}[Content]{0}[Column1],
    月リスト作成 = Table.FromList(
        {4..12} & {101..103}, Splitter.SplitByNothing(), {"日付"}
    ),
    年月作成 = Table.TransformColumns(
        月リスト作成, 
        {"日付", each Date.From(Number.ToText(年 + _/100, "0.00"))}
    )
in
    年月作成

「1~3」ではなく「101~103」としているのがミソ。こうすることで「1~3月」だけ3桁目の年が繰り上がるので年またぎに対応できる。

4~3月リスト

こんな感じ。月末リストが欲しい場合は

    月リスト = {2..12} & {101},
    月末リスト = List.Transform(月リスト, each
        Date.From(Text.From(年+_/100))-#duration(1,0,0,0)
    ),
    テーブル変換 = Table.FromList(
        月末リスト, Splitter.SplitByNothing(), type table[日付=Date.Type]
    )

こうすればできる。