Power Query で数字の羅列から日付を作成する

「20240501」とか「241205」のような数字の羅列をファイルの利用者に日付として読み取らせるようなデータが Excel のデータベースで管理されていたりします。これを「2024/05/01」とか「2024/12/05」のようにきちんと日付データとして扱いたいときに、 Excel のワークシート関数なら DATE 関数で対応します。

ここでは、Power Query (M 言語) で同じような処理をするにはどうするか、の一例をご紹介します。
今回は [Files] フォルダーに「yymmdd_管理番号.xlsx」というルールで保存されている複数のファイルがあるとして、このフォルダーに接続したクエリ (テーブル) で処理してみます。

[Source.Name] 列にフォルダー内のファイル名が格納されています。新しく [日付] 列を作成して、[Source.Name] 列のファイル名から日付を作成するのに必要な文字列を抜き出して処理してみます。

やり方は 1 つではないけれど、ワークシート関数の DATE 関数に似ている処理で対応するのなら、Power Query (M 言語) では #date 関数を使用します。

#date 関数では、年、月、日を表す整数を使用して日付を作成できます。ポイントは整数。

Excel のワークシート関数の場合、「=DATE(年 , 月 , 日)」の引数として指定する値は文字列でもよいです。たとえば、「20240801_01.xlsx」というデータを使うとき (この値がセル A10 に入っているとしたら) 、「=DATE(LEFT(A10,4) , MID(A10,5,2) , MID(A10,7,2)) のようにして、LEFT 関数や MID 関数で返る文字列を使用できます。

Power Query (M 言語) では、抜き出した年、月、日の値が文字列のままでは引数として使用して日付を作成できません。そのため整数にする処理も組み合わせる必要がありま。

列の作成と数式

1. Power Query エディターの [列の追加] タブの [全般] タブの [カスタム] 列をクリックします。

2. [カスタム列] ウィンドウが表示されます。[新しい列名] に作成する列名を入力して、[カスタム列の式] に数式を入力します。

#date の書式 (構文) は、「=#date(年 , 月 , 日)」です。

年、月、日に指定する数式や値について確認します。

ワークシート関数の LEFT 関数のように先頭から指定した数分の文字列を返すには Text.Start 関数を使用します。そのため、[Source.Name] 列の先頭から 2 文字を抜き出して「年」として使う文字列を抜き出すには、「Text.Start([Source.Name],2)」とします。(西暦が 4 桁で表されているのなら第 2 引数は 4 です。)

ワークシート関数の MID 関数のように指定した位置から指定した数分の文字列を返すには Text.Middle 関数を使用します。Text.Middle 関数は先頭の文字を「0」から数えます。そのため、[Source.Name] 列の左から 3 番目を開始位置として 2 文字を抜き出して「月」として使う文字列を抜き出すには「Text.Middle([Source.Name],2,2)」とします。同じように、「日」として使う文字列を抜き出すには、「Text.Middle([Source.Name],4,2)」とします。

これらはすべてこの段階では文字列です。これを整数にするために Int64.From 関数を使用します。よって、数式は次のように作成します。なお、ここでは、西暦部分が 2 桁で表されているので、整数に変換後に 2000 を足すことで「2024」になるよう補完しています。

=#date(
Int64.From(Text.Start([Source.Name],2))+2000,
Int64.From(Text.Middle([Source.Name],2,2)),
Int64.From(Text.Middle([Source.Name],4,2)))

3. 列が作成されて日付が表示されます。

ただ、こちらをワークシートに読み込むとシリアル値で表示されます。セルの書式設定で日付の表示形式を変えればよいでしょう。ワークシートに読み込む前にきちんと日付データとしたいのなら次の手順に記載している数式で対応してください。

前の手順で列の作成を行ったステップの数式を編集します。Table.AddColumn の処理に type date を追加して日付データに変換します。

= Table.AddColumn(
変更された型, "日付", each #date(
Int64.From(Text.Start([Source.Name],2))+2000,
Int64.From(Text.Middle([Source.Name],2,2)),
Int64.From(Text.Middle([Source.Name],4,2))),
type date)