[PowerQuery]エラーになる日付を除く

2021-02-07

はじめに

年,月,日が別セルに入っているデータをクエリで読込んで,日付型のデータに変えてひとつのセルに入れる.

元データ

元データ

変換後データ

変換後データ

11月31日は存在しないので、変換後データからは除外する。

結論

変換できない日付はnullにして、フィルタでnullを除く。Errorのままだとフィルタで除けない。 nullに変換する方法は以下の2通り。

  1. 日付に変換する際にtry ~ otherwiseを使い,変換できない値はnullにする
  2. 一旦全部日付に変換して,Errornullにする

元データの準備

年、月、日が別セルに入っているデータを想定する。存在しない日(11月31日)も入っている。

元データ

元データを読込む

元データを選択した状態で、クエリを使ってデータを読込む.手順は以下の通り。

  1. 「データ」タブ
  2. データの取得
  3. その他にデータソースから
  4. テーブルまたは範囲から
  5. 元データの範囲を指定して読込む.

データの読込み

読込んだデータ.

読込み後

日付型にできない値をnullに(try ~ otherwise)

まず,カスタム列を追加する.

カスタム列の追加

日付型に変換する式を追加する。追加する式は以下の通り。

try #date([年], [月], [日]) otherwise null

日付型に変換

変換できなかった日付にはnullが入る。

変換できなかったらnull

フィルタでnullを除く

列「日付」のフィルタでnullを除く。

フィルタでnullを除く

日付型に変える

日付型に変える

日付型に変更後,「閉じて読み込む」ボタンを押すと、存在しない日付を除いた一覧がワークシートにできる。

変換後データ

Errorをフィルタで除いたら

try~otherwiseを使わずに,11月31日のような存在しない日付を日付型に変更すると,Errorとなる。

Error発生

Errorはフィルタで除くことができない(リストに出てこない)。

Errorはフィルタに出ない

日付型にできない値をnullに(日付型に変換してからErrorをnullに変える)

try~otherwiseを使わずに,全ての値を日付型に変換した後でErrornull変えることもできる.まず,日付型に変換するためのカスタム列を追加する.

カスタム列の追加

日付型に変換する式を追加する。追加する式は以下の通り(try ~ otherwiseは使わない)。

#date([年], [月], [日]) 

日付型に変換

変換できなかった日付にはErrorが入る。

変換できなかったらError

「変換」タブの「値の変換」から「エラーの変換」を選ぶ.

エラーの変更

Errornullに変換する.

Errorをnullに変換

Errorになった,変換できなかった日付はnullになる。

変換できなかったらnull

後はフィルタでnullを除き,日付型に変えれば完了.

環境

  • Windows 10 Pro
  • Excel 365