【パワークエリ】日付データから年/月/週/日や年度の簡単抽出

日付データ 年 週 月 四半期 曜日 年度 抽出 取得 第何週PowerQuery/Pivot
スポンサーリンク

Excelで日付データから、月の第何週目かや、年度を取得するためには1個1個複雑なExcel関数を指定する必要がありめんどくさいですよね…。そんな場合に、Excelのパワークエリで日付列から年、四半期、月、週、日、曜日、年度を簡単に抽出できる方法を紹介します。これでExcelで簡単にカレンダーテーブルが作成可能になります!

【本記事でできること】
・年、四半期、月、週、日、曜日をパワークエリで簡単に取得できる
・年度をパワークエリで取得できる

・Excelで簡単にカレンダーテーブルが作成できる

週表示を月の通算週にするのか、年の通算週にするのかの設定や、年度の取得方法なども詳しく紹介しています。Excel関数で日付データを変換する手間が減り非常に便利です。PowerBI内のクエリも同様ですので、ぜひご参考にしてください!

パワークエリ 日付データから年/四半期/月/週/日/曜日の抽出

まずは、下記のように単純な日付別の売上データから、年、四半期、月、週、日、曜日などの列を追加します。

PowerQuery クエリ 週 年 月 抽出 日 曜日取得 PowerBI

それでは、ExcelのPowerQueryで年、四半期、月、週、日、曜日を抽出していきます。

今回パワークエリに取り込むデータは下記の日付別の売上データです。

取り込みデータ PowerQuery

クエリに取り込み、日付列をクリックした後
①「列の追加」タブ②「日付」から、③年、月、四半期、週、日などの抽出したいものをクリックするだけです!

PowerQuery 年 月 四半期 週 日 抽出 取得 日付データ パワークエリ PowerBI

年をクリックした結果が下記です。無事、日付データから年のみを抽出することができました。

PowerQuery 年 抽出 取得 年のみ

同様の方法で、四半期、月、週、日、曜日を追加すればOKです。

週の追加には
・年の通算週
・月の通算週
の2種類があります。これは、年の通算週だと年初めから第何週なのかが取得でき、月の通算週だと月初めから第何週なのかが取得できます。月の通算週にするのが使い勝手が良いかと思います。

週 抽出 月の第何週か PowerQuery 月の通算週 年の通算週

また、曜日は「日」の中にあるのでご注意ください。

これで、日付データから年、四半期、月、週、日、曜日を抽出することができました!

曜日 抽出 パワークエリ PowerBI

「閉じて読み込む」をクリックし、抽出したテーブルを作成できたことが確認できます。

PowerQuery 年 四半期 週 月 日 曜日 抽出 日付 PowerBI

【関連記事】
▶FORMAT関数で日付データから年/月/日/曜日に変換
▶パワークエリで日付文字列を分割、抽出
▶カレンダーテーブルの自動作成(テンプレコードあり)
▶DATEADD関数で前年/前月/前日比の取得(日付の引き算)
▶DATEDIFF関数で日付間の差分を取得(在庫管理表の作成)

【結論】
PowerQueryエディターの「列の追加」タブにある「日付」から、簡単に年や週を抽出できる!

スポンサーリンク

パワークエリ 日付データから年度の取得

続いて、日付データから年度データを取得していきます。

PowerQuery クエリ 年度 取得

年度は4月始まりなので、1~3月はその年から1年引く必要があります。少しテクニックが必要ですが、こちらも比較的簡単にできちゃいます。

方法は1年引いた年の列を作成し、月が1~3月の場合はそちらの列を指定するという方法です。

まずは、年-1のデータを作ります。
クエリ画面の
「カスタム列」→「年」→「挿入」で[年]を追加します。
1年引きたいので、カスタム列の式に④「-1」を入力します。

PowerQuery パワークエリ 年度 引き算 カスタム列

次に、列名がカスタムだと分かりづらいので、名前を「年ー1」に変えOKをクリックします。

PowerQuery パワークエリ カスタム列 名前変更

年の列から1年引かれた「年-1」という列を作成することができました。

次に、年度列を作成するため、1~3月はこの作成した「年-1」のデータを使い、それ以外は通常の「年」のデータを使うという条件列を追加していきます。

まずは、①「条件列」を追加します。
月のデータが3以下の時、「年-1」のデータを指定し、それ以外は「年」のデータを指定するため、条件式は下記になります。
②条件列名:月
③演算子:次の値以下
④値:3
⑤出力を列の値に変更し「年-1」を指定
⑥それ以外の場合:「年」を指定

PowerQuery 条件列 条件式 演算子 年 日付データ クエリ PowerBI

先ほどと同様に、新しい列名を「年度」に変更しOKすれば、年度データの取得ができました!

PowerBI PowerQuery 年度データの取得

【結論】
カスタム列と条件列を組み合わせれば、PowerQueryで簡単に年度列も追加できる!

スポンサーリンク

パワークエリで年/月/週/日/曜日や年度の抽出方法まとめ

今回はPowerQueryで年、四半期、月、週、日、曜日、年度を簡単に取得する方法を紹介しました。
・年、四半期、月、週、日、曜日はパワークエリの列の追加タブにある日付から取得!
・年度はパワークエリのカスタム列と条件列を組み合わせて取得!
・Excelで簡単にカレンダーテーブルの作成

今回を方法をマスターすれば、いちいちExcel関数で年や週、年度などの抽出をしなくてすむので便利です。また、元データの日付を追加し、更新ボタンをクリックするだけでカレンダーテーブルが自動更新されます!これもクエリで作成する大きなメリットですね!

PowerBIのクエリでも同様の設定方法ですので、ぜひご参考にしてください!

下記でPowerQueryやPowerBIの便利機能、DAX関数をまとめています。ぜひご参考下さい!

▶パワークエリ、パワーピポットまとめ

▶PowerBI、powerQueryの便利機能まとめ

▶よく使うDAX関数まとめ

良ければ、☟クリック応援よろしくお願いします!

にほんブログ村 サラリーマン日記ブログ IT系サラリーマンへ
にほんブログ村

コメント

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