意識高い系のコワーキングスペースが月額2,000円〜 全国規模で店舗も多くてテレワークが捗る!(詳細はリンク先にて)
Udemyで48時間セール開催!

対象コースが驚きの1,610円〜、9月20日まで。

『ゼロからの Tableau 入門』
『ゼロからの Tableau Prep 入門』が75%オフ中!
マイクロソフト関連コンテンツ、
そのほかのコンテンツもバーゲンセール中!

詳細はこちら

【PowerQuery/Tips】空のクエリから自動更新される日付テーブルを作る方法

モダンExcel
スポンサーリンク

パワークエリで空のクエリから自動更新される日付テーブルを作る方法を投稿します。

日付が含まれたデータを使って時系列グラフ描画する際に、「日付抜け」があると時系列表現を行う折れ線グラフなどで描画がしっくりこないことがあります。

いわゆるトランザクションが記録される明細データなどは、明細が発生する日のみが記録され、明細が全くない日であれば、その日付は抜けてしまいます。

BIツールなどを使う場面では、この「日付抜け」を補完するために日付のみのテーブルで補完することがあります。

PowerQueryでは、M言語を使うことによって指定した日付から現在日付までを動的に出力することが可能です。若干、違和感ある操作で忘れそうなのでメモとして投稿します。

スポンサーリンク

パワークエリで空のクエリから自動更新される日付テーブルを作る

手順1 空のクエリを作る

日付テーブルはエクセルシート上ではなく、パワーピボットのデータモデルとして使いたいので「空のクエリ」から作成します。

【PowerQuery/Tips】空のクエリから自動更新される日付テーブルを作る方法(最初に空のクエリを作る)

▲ 「データの取得」⇒「その他のデータソース(O)」⇒「空のクエリ(Q)」を選択します。

【PowerQuery/Tips】空のクエリから自動更新される日付テーブルを作る方法(主に数式バーを使う)

▲ Power Queryエディターが開きます。日付テーブルの作成は数式バーから行います。

手順2 開始日を指定する

M言語の日付関数である「#date」関数で日付値を作ります。

#date関数

構文

date(year as number, month as number, day as number) as date

説明

年 year、月 month、日 day から日付値が作成されます。 次の条件を満たさない場合、エラーが生じます。

1 ≤ 年 ≤ 9999
1 ≤ 月 ≤ 12
1 ≤ 日 ≤ 31
【PowerQuery/Tips】空のクエリから自動更新される日付テーブルを作る方法(M言語で開始日を指定する)
M言語で開始日を指定する

▲ 今回は「2019年1月1日」を基準日としてテーブルを作成します。数式バーに「#date(2019,1,1)」と入れます。Enterを押すと、日付のみが返されるシンプルな処理です。

【PowerQuery/Tips】空のクエリから自動更新される日付テーブルを作る方法(ステップ名はソース)

PowerQueryエディター上のステップは、「ソース」という名前で記録されました。この「ソース」を次のM関数で使います。

手順3 何やらコードを打ち込む

ここからはM言語がわからないとさっぱりな感じ。私もまだ不慣れ。

【PowerQuery/Tips】空のクエリから自動更新される日付テーブルを作る方法(fxを押してステップを追加する)

▲ 数式バーの「fx」を押します。

これは「ステップを追加する」ボタンらしく、押すたびにステップが追加されます。押すのは一回でOKです。追加されたステップはその前のステップを参照しています。

そして次のコードを打ち込みます。

= List.Dates(ソース, Number.From(DateTime.LocalNow()) - Number.From(ソース), #duration(1,0,0,0))

▲「ソース」は、前のステップで打ち込んだ値(2019年1月1日)を参照しています。

内容的には、「ソース」で指定した日付(2019年1月1日)から始まり、1日ずつ増分されるdata値のリストが現在日まで返されます。

【PowerQuery/Tips】空のクエリから自動更新される日付テーブルを作る方法(何やら長いコードをいれる)
何やら長いコードをいれる

▲ 2019年1月1日から現在日までのリストが作られました。エクセルのデータ更新ボタンを押すたびに範囲が変更します。

M関数の意味を知りたいからは以下、押すと開きます。

構文

List.Dates(start as date, count as number, step as duration) as list

説明

「start」で指定した日付から始まり、「step」間隔で増分されるdata値のリストが「count」回数分返されます。

利用例

元旦 (#date(2021, 1, 1)) から始まり、1日ずつ増分される5個のdate値のリストを作成します。

List.Dates(#date(2021, 1, 1), 5, #duration(1, 0, 0, 0))

構文

Number.From(value as any, optional culture as nullable text) as nullable number

説明

  • 指定した value から number 値が返されます。
  • オプションの culture を指定することもできます (例: “en-US”)。
  • 指定した value が null の場合、null は Number.From が返されます。
  • 指定した value が number の場合、value が返されます。

利用例

#datetime(2020, 3, 20, 6, 0, 0) の number 値を取得します。

Number.From(#datetime(2020, 3, 20, 6, 0, 0))

構文

DateTime.LocalNow() as datetime

説明

システムの現在の日付と時刻に設定された datetime 値を返します。

構文

#duration(days as number, hours as number, minutes as number, seconds as number) as duration

説明

days(日)、hours(時)、minutes(分)、seconds(秒)の数値から期間値が作成されます。

参考 Power Query M 関数参照

手順4 テーブルへの変換、データ型を日付へ

コードを打ち込んだだけではまだ使えず、もうひと手間あります。

【PowerQuery/Tips】空のクエリから自動更新される日付テーブルを作る方法(リスト変換ツールからテーブルに変換する)

▲ コードを打ち込むとリボンメニュー「(リストツール)変換」タブが増えています。この右端の「テーブルへの変換」を押します。

【PowerQuery/Tips】空のクエリから自動更新される日付テーブルを作る方法(リスト変換ツールからテーブルに変換する)

▲ 設定画面が出ますが、特に設定せずにOKを押します。

【PowerQuery/Tips】空のクエリから自動更新される日付テーブルを作る方法(列のデータ型、名称をつける)
列のデータ型、名称をつける

▲ 後は、列名称を付けてデータ型を「日付」に変更すれば、使えるようになります。

手順5 フィルター・スライサー用に列を追加しておく

これはオプションですが、日付テーブルにフィルターやスライサーで使いそうな列を追加しておくと便利になります。

【PowerQuery/Tips】空のクエリから自動更新される日付テーブルを作る方法(GUIから年・月の列を追加する)

▲ 年、月などはGUIから追加できます。

【PowerQuery/Tips】空のクエリから自動更新される日付テーブルを作る方法(会計年度をカスタム列で追加する)
会計年度をカスタム列で追加する
【PowerQuery/Tips】空のクエリから自動更新される日付テーブルを作る方法(会計四半期を条件列から追加する)
会計四半期を条件列から追加する

▲ 会計年度はカスタム列、会計四半期は条件列を使えば追加できます(データモデル入れた後に、DAXでも追加できます)。

ここまで出来たらシート内に読み込むか、データモデルに追加すればピボットテーブルで使えるようになります。

まとめ

M言語はとっつきづらいですが便利ですね。

VBAとかでもできるでしょうが、GUI+ローコードで複雑なことができるのは魅力です。

タイトルとURLをコピーしました