[Power BI] 日付型のデータを用意する方法

[Power BI] 日付型のデータを用意する方法:

この記事は、2018 年 1 月 16 日 に Data Platform Tech Sales Team Blog にて公開された内容です。

Microsoft Japan Business Intelligence Tech Sales Team 伊藤

以前の記事 [Power BI] DAX入門(2) カレンダーテーブルの作成 にてカレンダーテーブル (日付マスタ) の必要性とその作り方をご紹介しています。このカレンダーテーブルを使うには、分析対象のデータに結合するための列、すなわち日付型の列が必要です。

また、日付型のデータがあると、例えばこんなことができます。

カレンダーを使った日付指定	今日を基準とした相対的な日付指定	時系列予測

さらにカスタム ビジュアルを使えばこんな表現もできます。

Beyondsoft Calendar	Calendar by Tallan

今回は、こんなに便利な日付型の列を作成する方法をクエリ エディターと DAX の2通りでご紹介します。ただし DAX は 1990 年 3 月 1 日以降の日付をサポートするため、それ以前のデータを含む場合はクエリ エディターを使用します。

例1：年、月、日 をそれぞれ数値で表す列から、日付型の列を作成

下記のような列を持つテーブルを想定しています。(実際はこの他に、商品名や売上額など、様々な列があると思います。)

年	月	日
2017	11	1
2018	1	11
2018	2	3

クエリ エディターで作成

「例から列を追加」します。これは Excel 2013 の新機能である「フラッシュ フィル」と似た機能です。

上記の 3 つの列を選択し、右クリック メニューから [例から列を追加する…] をクリックします。

右側に表示される空の列「列1」に「2017/11/1」と入力します。ポイントは「2017/11/01」などとせず、列にある値そのままを入力することです。

(なので、もし元の列が文字型で「01」という値が入っている場合は「2017/11/01」と入力するようにします。)

この例では以下のように、こちらの意図を汲み取ってすべての行にデータが入力され、どのような変換式を作成したのかが表示されます。(列名は自動的に「結合済み」に変わります。)

この変換で問題なければ [OK] をクリックします。(思い通りにならないときは、追加で何行かサンプル値を入力してみてください。)

データ型を「日付」に変更し、必要に応じて列名を変更します。下図は変更後のイメージです。これで日付列を使ってカレンダーテーブルと結合できます。

DAX で作成

DATE 関数 を使用すれば、一発で解決します。[新しい列] で次のような式を入力します。

日付 = DATE([年],[月],[日])

データ型を「日付」に、書式を「yyyy/M/d」に変更します。(リレーションを作成するにはデータ型が同じである必要がありますが、書式は無関係です。)

例2：年月日を8桁の数字 (例：20171101) で表す列から、日付型の列を作成

クエリ エディターで作成

例1 と同様に「例から列を追加」します。今度はサンプル値として「2」と入力すると複数の候補が表示されるので、そこから選択します。

上から2番目の「2017/11/01 (年月日 からの日付)」を選択し、[OK] をクリックします。

目的の列ができました。

DAX で作成

例1 で使った DATE 関数 を使いますが、その引数となるデータを作るには、割り算や MOD 関数 を使うことも、文字列のように LEFT 関数、RIGHT 関数 を使うこともできます (暗黙的な型変換が行われます)。ここで紹介する以外にも色々な方法があると思います。

割り算と MOD 関数 を使用した例

列を追加し、次の式を入力します。

日付 = DATE([年月日]/10000, MOD([年月日]/100,100), MOD([年月日],100))
 

DATE 関数の最初の引数は小数点以下を切り捨てるので、10000 で割るだけで小数点以下の処理はしていません。

MOD 関数 は、1 つ目の引数を 2 つ目の引数で割った余りを返します。100 で割った余りで、整数部の下 2 桁を取得します。

データ型を「日付」に変更します。

LEFT 関数 と RIGHT 関数 を使用した例

列を追加し、次の式を入力します。(下線部が DATE 関数の 3 つの引数です。)

日付 = DATE(LEFT([年月日],4), RIGHT(LEFT([年月日],6),2), RIGHT([年月日],2))
 

DATE 関数の真ん中の引数 (太字部分) で月に相当する値を渡しますが、まず LEFT 関数で [年月日] 列の左から 6 文字を取得し (「20171101」⇒「201711」)、RIGHT 関数でさらにそこから右 2 文字を取得 (「201711」⇒「11」) しています。

データ型を「日付」に変更します。

---

最後に、カレンダーテーブルとリレーションを作成したら、混乱を避けるためカレンダーテーブル以外の日付列は非表示にしましょう。