システムからエクスポートしたデータの日付が使いにくいこと無いですか？
今回は、特に扱いに困った日付データを変換する方法についてお届けします。
この記事でやりたいこと
以前扱いに困ったデータにこんな日付のものがありました。
どこのとは言いませんが、某会社の会計システムから取り出した仕訳データの日付がこれなのです。
使いにくいったらありゃしない。笑
で、これを正規の日付データに変換できないか。
こんな感じに、です。
では実際の方法を見ていきましょう。
使うパワークエリの機能はこの通りです。
- 置き換え機能を使った不要文字の削除
- 区切り文字を使った列の分割
- カレンダーテーブルとのマージ
- （１）列追加（カスタム列）による日付データの列の作成
- （２）列マージによる日付データ列の作成
日付データを変換する手順
事前準備
まずは、対象となるデータとカレンダーテーブルをパワークエリで取得します。
取得方法は以前の記事を参考にしてください。
このカレンダーテーブルは、このように月に対応する年度指定するテーブルです。
これによって、月日表示しかないこのデータに年度情報を追加します。
パワークエリに取り込むと、このように2つのクエリがある状態になります。
ここでは変換したい日付のデータテーブルが「テーブル1」、年度を追加するためのカレンダーテーブルが「テーブル２」です。
クエリ名をそのように変更してもいいかもしれませんね。
そして、カレンダーテーブルについてはパワークエリ内でしか使用しませんので、閉じ方は「接続の作成のみ」（データモデルへの追加も無し）とします。
そして、テーブルの変換作業はテーブル１でやっていきます。
不要文字の削除
まずは日付データ先頭の「＊」がいらない（むしろ邪魔）なので、これを消します。
方法は、「値の置換」を使います。
変換タブより選択してください。
そして検索する値に「＊」、置換後は入力無しでOKを押します。
これで「＊」の除去が完了です。
月・日で列の分割
次にこの列を月と日の列に分割します。
ホームタブ、列の分割より「文字による分割」を選択します。
区切り文字をカスタムとし、「 . 」を入力します。
ちなみに分割は、今回区切り文字が1か所しか出てきませんので、どれでも構いません。
区切り文字が複数ある場合は、一回だけ分割するのか複数回分割するのか、この設定を確認しましょう。
これでこのように月と日に分割できました。
ここで、分かりやすいように列タイトルを月と日に変えておくと後が分かりやすいですね。
あと、確認しておきたいのがデータの型です。
後ほどこの列から年を追加し、日付データに変換しますのでデータの型が文字列であれば数値に変えておきましょう。
列タイトルの型表示が「ABC」（文字列）であれば「123」（数値）に変えておきます。
クエリのマージによる年列の追加
ホームタブの「クエリのマージ」を選択し、マージ対象のテーブル「テーブル3」を選択し、検索列をそれぞれ選択します。
ここではテーブル１の「月」列と「テーブル３」の「月」列が照合列と言うことになります。
この照合列を使ってテーブル１とテーブル３を結合し、月に対応する年を引っ張ってきます。
注意点は、この照合列のデータ型は一致してないといけません。文字列ー数値だとエラーになるので、今回は数値で合わせています。
マージするとこのようになりますので、テーブル３を展開します。
テーブル３を展開、年次を表示させるとこんな感じです。
これで年・月・日の表示までできました。
あとはこれに基づいて年月日列を作成します。
なお、クエリのマージについてはこちらの記事も併せてどうぞ。
年月日列の作成
列の追加タブより「カスタム列」を選択します。
ここでは列名を「年月日」としました。
そしてカスタム列の式には以下の文を記載します。
= Date.From(#date([年],[月],[日]))
これは指定した日付を返す関数で、ExcelのDate関数と同じようなものです。
#dateは、これでデータ型を数値から日付に変換しています。
これで年月日列が追加されました。
ちなみにdate.from関数を使わなくてもこのような書き方でも同じ結果を得られます。
= [年]&"/"&[月]&"/"&[日]
ただしこの方法による場合、データ型が数値のままではエラーになりますので、年・月・日列のデータ型をテキストに変換しておく必要があります。
また、作成された列も文字列ですので、データ型を日付に変換しないといけません。
ここまでで日付データの変換が完了しました。
では、列のマージ（結合）を使った方法も見ておきましょう。
列のマージによる年月日列の追加
年月日列を作るもう一つの方法を確認しておきましょう。
上の方法はカスタム列の追加を使った方法で、関数を知らなければ使えないし、エラーが出た時の対処が難しいという欠点があります。
なので出来るだけ既にあるメニューだけで年月日列を作りたいですよね。
そこで使うのが「列のマージ」（結合）という機能です。
まずはこの列を結合したい順番に並び替えます。
「年」列を選択し、変換タブの並び替えより先頭へを選択します。
次に、列の追加タブから列のマージを選択します。
区切り記号はカスタムを選択し、「 / 」を、列名は「年月日」としてOKします。
すると、このように年月日列が追加されました。
このままだと年月日列が文字列のままですので、データの型を日付に変換しておきます。
年月日列を選択した状態で、変換タブのデータ型より「日付」を選択します。
すると、このように年月日列が日付データに変換されました。
あとはこれを目的に応じてExcelに読込むなどして終了です。
さいごに
今回は日付データの変換方法をお届けしました。
今回使った文字の置き換えや列の分割、クエリ・列のマージといった方法は、パワークエリを使う上でぜひとも知っておきたい機能ですので、ぜひ使いこなせるようになっていただきたいと思います。
最後までお読みいただきありがとうございました。
