Power BI 様々な日付形式でのデータ取得を試してみた。

自己紹介

　佐賀県の小売業の情シスをしています。

　主に、基幹システムの開発・運用・保守をしており、情シス以外でも簡単にデータを抽出し分析出来ることを目標に、Power BIを勉強中です。

今回の内容

　様々な業務データやを参照して、データ分析を行うことがあるが、特に日付について、様々な形式で保存されていることが多く、Power BI側に取り込む際に工夫が必要だったので、今回様々な日付形式でデータ取込を試してみた。

今回取り込んだサンプルの日付データ

日付データサンプル（西暦・和暦).txt

1320 Bytes

ファイルダウンロードについて

ダウンロード

日付形式の種類について

１．日付（西暦）

 一般的によく見かける日付形式。

PowerBIでのデータ取得

特に加工など必要なく、日付型としてデータ取得が正しく行われた。

２．日付（和暦）

日本独自の暦、元号をアルファベットで表記や漢字で表記したり、
元号の初年は、元年表記する若しくは１年と表記する方法がある。

Power BIでのデータ取得

日本独特の暦のため、正しく取り込めるかが不安だったが、元年表記以外については、特に加工もせず正しく取り込むことができ、しかも西暦に変換も行ってくれた。

元年表記の場合、日付型に強制変更した場合には、元年についてのみ、Errorで表示されてしまうので注意が必要。

　

３．日付８桁（西暦）

　西暦で、年月日に区切り（スラッシュ・ハイフン等）がなく、数値をそのまま結合した日付形式

　例：19120729　　→　1912年7月29日

Power BIでのデータ取得

当然ながら、そのままだと日付型では認識せず、数値として認識してしまうので、１の西暦型に変換し直して取り込む必要がある。

エクセルの場合

=TEXT(セル値,"0000!/00!/00")

SQLの場合（文字型の場合）

SELECT 変換後日付 = CONVERT(DATE,日付_西暦8桁) FROM 日付

  

４．日付７桁（和暦）　

元号を数値（明治が１、大正が２等）にし、年月日を結合して表示する形式

元号の数値についても、統一性がなくバラバラになっている場合もある。
例：測量　花子　　生年月日：5430402　→　昭和43年4月2日

Power BIでのデータ取得

　当然ながらPower BIにてデータ取得しても、日付型としては認識しない。

　西暦に変更しようと考えたが、標準の和暦フォーマットなら取込可能なので、和暦形式を変更する。

エクセルの場合

=SWITCH(VALUE(
 LEFT(セル値,1)),1,"M",3,"T",5,"S",7,"H",9,"R") 
 &TEXT(RIGHT(セルの値,6),"00!.00!.00")

ＳＱＬの場合（文字型の場合）

SELECT 
変換日付 =  CASE  LEFT(日付_和暦7桁,1) 
 WHEN 1 THEN 'M'
 WHEN 3 THEN 'T' 
 WHEN 5 THEN 'S' 
 WHEN 7 THEN 'H' 
 WHEN 9 THEN 'R'
 END + FORMAT(CONVERT(INT,RIGHT(日付_和暦7桁,6)),'00/00/00')
FROM
日付

無事、PowerBIへのデータ取得に成功した。

まとめ・感想

　西暦のみしかPowerBIへのデータ取得が出来ないと思っていたが、和暦表記も取り込めることが出来た。

ただ、日付７桁（和暦）については、日本独特であり、さらに日付型に変換するのに手間がかかった。