【Excel】Power Queryで祝日表との結合が合わない時の日付型確認

ExcelのPower Queryを使って祝日表を結合しようとしたとき、日付が一致せずにデータが結合されないトラブルはよく発生します。多くの場合、原因は日付のデータ型が異なる、または日付が数値やテキストとして認識されていることです。この記事では、Power Queryで祝日表を正しく結合するための日付型の確認方法を、具体例を交えて詳しく説明します。会社のPCで作業されている方でも、管理者に頼らず自分で確認できる手順を紹介しますので、ぜひ参考にしてください。

1. 祝日表が結合されない原因:日付型の不一致

Power Queryで2つのテーブルを結合する際、結合キーとなる列のデータ型が完全に一致していなければなりません。祝日表と日付データを結合する場合、以下のような型の不一致がよく起こります。

• メインデータの日付列が日付型(date)なのに、祝日表の日付列がテキスト型(text)になっている。 例えば、メインデータが「2024/01/01」の日付型、祝日表が「2024-01-01」のテキスト型では、見た目が似ていても結合できません。
• メインデータの日付列が日付/時刻型(datetime)で、祝日表が日付型(date)の場合。 時刻部分の有無で不一致が発生します。
• どちらかの日付が数値(シリアル値)として扱われている場合。 例えば、CSVから読み込んだ日付が「45292」のような数値になっていると、日付として認識されません。
• ロケール(地域設定)の違いによる日付形式の解釈の違い。 例えば、米国形式「01/02/2024」が1月2日なのか2月1日なのか、Power Queryの設定によって変わる場合があります。

これらの問題を解決するには、Power Queryエディタ内で両方の日付列を同じデータ型に統一する必要があります。

2. 日付型を確認する手順(Power Queryエディタ)

まずは、実際にPower Queryエディタを開いて日付列のデータ型を確認しましょう。以下の手順で作業します。

1. Excelを開き、リボンの「データ」タブから「データの取得と変換」グループにある「テーブルまたは範囲から」(または「データの取得」→「その他のデータソース」)をクリックして、メインデータと祝日表の両方をPower Queryエディタに読み込みます。
2. 左側の「クエリ」ペインで、メインデータのクエリを選択します。日付列のヘッダーに表示されているアイコンを確認します。日付型の場合はカレンダーアイコン、テキスト型の場合は「ABC」アイコン、数値型の場合は「123」アイコンが表示されます。
3. 同様に、祝日表のクエリを選択し、日付列のアイコンを確認します。両方のアイコンが同じ日付型(カレンダーアイコン)になっているかどうかが重要です。
4. もし祝日表の日付列がテキスト型や数値型になっている場合、列ヘッダーのアイコン部分(または列名の左側)をクリックします。表示されるメニューから「日付」を選択して型を変更します。
5. 型を変更したら、Power Queryエディタの左側にある「閉じて読み込む」の代わりに「閉じて次に読み込む」を選択し、「接続の作成のみ」を選んでクエリを保存します。その後、結合を行うためのマージ操作を行います。

注意点として、型を変更するときにエラーが発生する場合があります。その場合は、日付の形式が正しく認識されていない可能性があります。次のセクションで具体的なトラブルシューティングを説明します。

3. 具体的なトラブルシューティングと解決方法

3-1. テキスト型の日付を日付型に変換する方法

祝日表がCSVや手入力で作成されている場合、日付がテキスト型になっていることが多いです。例えば「2024/1/1」のような文字列です。これを日付型に変換するには、Power Queryエディタで列を選択し、ホームタブの「データ型」から「日付」を選ぶだけではうまくいかないことがあります。その場合は、以下の方法を試してください。

• 「列の追加」タブから「日付」→「日付のみ」を選択する。 これにより、既存の列を変換するのではなく、新しい日付列が追加されます。元のテキスト列は残しておき、新しい列を結合キーとして使います。
• 「変換」タブの「テキストから日付」を使用する。 ただし、この機能は地域設定に依存するため、期待通りの変換にならない場合は、次の「ロケールの指定」を試します。
• ロケールを指定して変換する。 列を右クリックし、「データ型の変更」→「ロケールを使用」を選択します。表示されるダイアログで、元のデータの形式(例:日本語の場合は「日本語(日本)」)と変換後のデータ型(日付)を指定します。

3-2. 数値(シリアル値)を日付型に変換する方法

Excelの日付シリアル値は、1900年1月1日を1とする数値です。たとえば「45292」は2024年1月1日を表します。Power Queryで数値として読み込まれた場合、その列のデータ型を「日付」に変更するだけで正しく変換されることが多いです。ただし、元の数値が整数でない場合(時刻を含む小数)は「日付/時刻」型に変換する必要があります。

3-3. 日付/時刻型と日付型の不一致

メインデータの日付列が「日付/時刻」型(例:2024/1/1 0:00:00)で、祝日表が「日付」型(例:2024/1/1)の場合、結合キーとして一致しません。この場合は、メインデータの日付/時刻列を「日付」型に変換するか、祝日表の日付列を「日付/時刻」型に変換します。一般的には、日付/時刻列から日付部分だけを抽出する方がわかりやすいでしょう。日付/時刻列を選択し、「変換」タブの「日付」→「日付のみ」をクリックして新しい列を作成します。

4. 結合操作の正しい手順

データ型が統一できたら、いよいよ結合(マージ)を行います。以下の手順で進めてください。

1. Power Queryエディタで、メインデータのクエリを選択した状態で、ホームタブの「結合」→「クエリのマージ」をクリックします。
2. 表示されるダイアログで、下部のドロップダウンリストから祝日表のクエリを選択します。
3. メインデータの日付列と祝日表の日付列を、それぞれクリックして選択します。両方の列が同じデータ型であることを確認してください。型が異なる場合は、ここで警告が表示されることがあります。
4. 結合の種類を選択します。通常は「左外部」(メインデータのすべての行を保持し、一致する祝日情報を追加)を選びます。「内部」を選ぶと、祝日がない日付の行が削除されるので注意が必要です。
5. 「OK」をクリックしてマージを実行します。すると、メインデータのクエリに新しい列(テーブル型)が追加されます。列ヘッダーの右側にある展開ボタン(二重矢印)をクリックし、必要な祝日情報の列だけにチェックを入れて「OK」をクリックします。

これで結合が完了します。もし結合結果が正しくない(祝日が表示されない、または多くの行が結合されない)場合は、データ型の確認をやり直してください。

5. 状況別の比較表:日付型と結合結果

メインデータの日付型	祝日表の日付型	結合結果	推奨対応
日付 (date)	日付 (date)	正常に結合	そのまま結合可能
日付 (date)	テキスト (text)	結合されない	祝日表の日付列を日付型に変換
日付/時刻 (datetime)	日付 (date)	結合されない(時刻部分が不一致)	メインデータの日付列を日付のみに変換、または祝日表を日付/時刻に変換
数値 (123)	日付 (date)	結合されない	数値を日付型に変換(「日付」データ型を割り当てる)
テキスト (text)	テキスト (text)	見た目が同じなら結合されるが、内部表現が異なる場合がある	両方とも日付型に変換することを推奨

6. 失敗パターンとその対策

失敗パターン1:型を変更したのにエラーが出る

テキスト列を日付型に変更しようとしたとき、「変換エラー」が表示されることがあります。これは、テキストの中に日付として解釈できない値(例えば「未定」や空白)が混ざっている場合に発生します。この場合は、まず列を選択し、「ホーム」タブの「エラーの管理」→「エラーの置換」を使って、エラーになる値を特定の値(nullや空文字)で置き換えてから型変換を行います。

失敗パターン2:日付の形式が異なる(年/月/日 vs 月/日/年)

例えば、メインデータが「2024/01/02」で、祝日表が「01/02/2024」の場合、どちらも日付型に変換できたとしても、実際の日付が異なる場合があります。これは、Power Queryのロケール設定が異なるために起こります。解決策としては、上記で紹介した「ロケールを使用」オプションで、元のデータの地域を指定して変換します。または、日付を「yyyyMMdd」のような標準形式に統一するために、テキストとして列を分割してから再構成する方法もあります。

失敗パターン3:結合後に祝日が重複して表示される

祝日表に同じ日付が複数存在する場合(例えば、元旦と元日が別々に登録されている)、結合によって1行のデータに複数の祝日が紐づき、行が増えてしまいます。この場合は、結合の前に祝日表で重複を削除するか、集計しておく必要があります。Power Queryエディタで祝日表を選択し、「ホーム」タブの「行の削除」→「重複の削除」で、日付列をキーにして重複を除去します。

7. 管理者に確認すべきポイントと注意点

会社のPCでPower Queryを使用する場合、以下の点についてはシステム管理者やデータベース管理者に確認してください。

• 共有データソースの変更権限: 祝日表が共有ドライブやSharePoint上にある場合、そのファイルを直接編集して型を変更すると、他のユーザーに影響を与えることがあります。Power Query内で型変換を行うことで元のファイルを変更せずに済むので、その方法を選びましょう。
• Power Queryのバージョンと機能制限: 古いバージョンのExcelでは、Power Queryの一部機能が利用できない場合があります。また、会社のポリシーでアドインが制限されている可能性もあります。管理者に最新バージョンの使用可否を確認してください。
• データソースの接続設定: 祝日表がデータベースやWebサービスから取得している場合、接続の認証情報や更新スケジュールについて管理者の設定が必要なことがあります。

8. よくある質問(FAQ)

Q1: Power Queryで日付型を変更しても、エクスポートすると元に戻ってしまうのはなぜですか?

Power Queryで型を変更した結果は、Excelのテーブルに読み込まれた時点で適用されます。ただし、読み込み先のExcelシートで日付の表示形式が変更されている可能性があります。セルの書式設定を確認し、必要に応じて「日付」表示形式に変更してください。また、再度クエリを更新すると型変換が再実行されるため、問題はないはずです。

Q2: 祝日表がネット上のCSVファイルで、毎年更新されます。結合の都度、型変換が必要ですか?

一度Power Queryでクエリを作成し、型変換の手順を組み込んでおけば、以後データを更新(「データ」タブの「すべて更新」)するたびに自動的に型変換が適用されます。元のCSVファイルの形式が変わらない限り、再設定は不要です。

Q3: 結合してもなぜか一部の日付だけ祝日が表示されません。

考えられる原因は、その日付が祝日表に存在しない、またはデータ型の不一致が一部の行で発生していることです。フィルターをかけて、一致しなかった日付を確認しましょう。また、祝日表の日付に余分なスペースや非表示文字が含まれていないかも確認してください。

まとめ

Power Queryで祝日表とデータを結合する際、日付型の不一致が最も一般的な原因です。メインデータと祝日表の日付列を同じデータ型(日付型が推奨)に統一することで、ほとんどの問題は解決します。型変換はPower Queryエディタ内で行い、元のデータソースを変更しないように注意してください。また、結合前に重複の削除やエラー処理を行うことで、より正確な結果が得られます。今回紹介した手順を実践すれば、会社の業務でよくある「祝日が入らない」という悩みから解放されるでしょう。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】