【Excel】Power Queryで日付列が文字列として読み込まれる時の型変換確認

Power Queryを使ってExcelのデータを読み込むと、日付列が文字列として認識される現象がよく発生します。日付として扱えないと、並べ替えや集計、フィルター処理に支障をきたすため、迅速に型変換を行う必要があります。この記事では、原因の切り分け方から具体的な変換手順、失敗しがちなポイント、そして管理者に確認すべき設定までを解説します。実際の操作を想定した内容で、すぐに実践できる情報を提供します。

なぜ日付列が文字列として読み込まれるのか

Power Queryが日付列を文字列と判断する原因は、主に次の3つに分類できます。まず、ソースとなるExcelファイルのセル書式が「文字列」になっている場合です。たとえ表示上は日付のように見えても、セルの書式設定が文字列であれば、Power Queryはそのままテキストとして取り込みます。次に、Power Queryの読み込み設定で「データ型検出」が無効または「固定の区切り記号に基づく」などに設定されているケースがあります。この場合、列の先頭数行のデータから型を推測するため、日付らしい値が先頭にないと文字列扱いされます。最後に、Excelの地域設定とPower Queryのロケールが異なる場合です。たとえば、米国形式(m/d/yyyy)で書かれた日付を日本形式(yyyy/m/d)として解釈しようとすると、変換に失敗して文字列のまま残ることがあります。

ソースデータのセル書式が文字列の場合

最も多い原因は、ソースのExcelファイルで日付列が文字列として入力されていることです。例えば、CSVから取り込んだデータや、他システムから出力されたデータでは、数字やスラッシュが単なるテキストとして保存されていることがよくあります。この場合、Power Queryはそのままテキスト列として読み込みます。確認方法は、Excelで該当セルを選択し、数式バーに表示される値が実際の日付シリアル値(44000など)ではなく「2024/01/15」のような文字列であれば、書式が文字列になっています。また、セルの左上隅に緑色の三角が表示されている場合も、文字列として保存されている可能性が高いです。

Power Queryの自動型検出が無効の場合

Power Queryでは、データを読み込む際に「データ型検出」の設定に従って各列のデータ型を推測します。既定では「先頭1000行に基づく」が選択されていますが、この設定が「固定の区切り記号に基づく」や「データ型検出を行わない」になっていると、日付列が文字列になることがあります。特に、新しいクエリを作成するときに表示される「データの変換」ダイアログで、省略して読み込むと自動検出が無効になる場合があります。また、既存のクエリを編集する際に、前の手順で列の型が明示的に「text」に設定されていると、その後ソースデータが日付に変わっても自動では更新されません。

地域設定と日付形式の不一致

会社のPCでExcelの地域設定が日本(yyyy/mm/dd)になっている場合、Power Queryもそのロケールを引き継ぎます。しかし、ソースデータが米国形式(mm/dd/yyyy)やドイツ形式(dd.mm.yyyy)で記述されていると、日付として認識できず文字列として扱われます。例えば「03/04/2024」という値は、日本では3月4日、米国では4月3日と解釈されるため、Power Queryは自動変換を諦めてテキストのままにします。この問題は、特に国際的なデータを扱う部署や、海外の取引先から受け取ったファイルで頻発します。

まず確認すべきこと

解決の第一歩は、問題の原因を正確に把握することです。以下の3点を順に確認してください。

1. ソースExcelのセル書式を確認する:該当列を選択し、[ホーム]タブの[数値]グループで表示形式を確認します。「文字列」になっている場合は、Power Queryの読み込み前に「日付」に変更すると改善する可能性があります。ただし、元データを変更できない場合は、Power Query側で変換します。
2. Power Queryエディタで現在のデータ型を確認する:クエリエディタを開き、該当列のヘッダー左側に表示されているアイコンを見てください。「ABC」が「123」であればテキスト、「123」であれば数値、「カレンダー」アイコンであれば日付です。アイコンがテキストになっている場合、その列は文字列として読み込まれています。
3. 「クエリの設定」の「検出されたデータ型」を確認する:右側の「クエリの設定」ペインの「適用したステップ」で、一番上の「ソース」ステップの歯車アイコンをクリックします。表示されるダイアログで「データ型検出」の設定がどうなっているか確認します。「先頭1000行に基づく」以外になっている場合は、これを変更して再読み込みすると、自動的に日付として認識されることがあります。

Power Queryエディタでの型変更手順

原因が特定できたら、Power Queryエディタ内で列のデータ型を変更します。以下の手順に従ってください。なお、元のExcelファイルは一切変更されません。

1. Excelの[データ]タブから[データの取得と変換]グループの[クエリと接続]をクリックし、該当クエリをダブルクリックしてPower Queryエディタを開きます。
2. 文字列として読み込まれている日付列のヘッダー左側にある「ABC」アイコンをクリックします。表示されるメニューから「日付」を選択します。これで、その列のデータ型が日付に変更されます。
3. 型を変更すると、プレビュー画面に「変更の種類」という適用したステップが追加されます。このとき、一部の行にエラー(「Error」と表示)が発生することがあります。エラーが発生した場合は、セルに日付として解釈できない値(例えば「N/A」や空白など)が含まれている可能性があります。
4. エラーがある場合は、列の変換前に「nullの置換」や「エラーの置換」を行う必要があります。具体的には、該当列を右クリックし、「値の置換」で特定の文字列をnullに変えるか、または「列の追加」タブから「条件列」を使ってエラーを回避します。
5. 型変更が完了したら、[ホーム]タブの[閉じて読み込む]をクリックして結果をワークシートに反映させます。クエリの更新が必要な場合は、[データ]タブの[すべて更新]を実行してください。

地域設定を考慮した変換

標準の「日付」型変換でうまくいかない場合は、ソースデータの日付形式が現在のロケールと異なる可能性があります。その場合は、列を右クリックして「ロケールを使用したデータ型の変更」を選択します。表示されるダイアログで、データの実際の形式(例:「英語 (アメリカ)」で「M/d/yyyy」)を指定してから「日付」型を選択します。これにより、Power Queryが正しく解釈できるようになります。

エラーの種類	原因	対処法
「Error」セルが発生する	日付として解釈できない文字列(例:「未入力」「-」)が含まれている	変換前に「値の置換」で該当文字列をnullに置き換える。または「エラーの置換」でエラーを別の値(当日日付など)に変える。
変換後も日付にならない	日付形式がロケールと合っていない(例:米国形式のデータを日本ロケールで変換)	「ロケールを使用したデータ型の変更」で適切なロケールと形式を指定する。
クエリ更新後に型が文字列に戻る	ソースデータに新しい行が追加され、その行の値が日付として認識されない	「変更の種類」ステップの後に「エラーの置換」を追加するか、日付変換を安全な関数(Text.ToDateなど)を使ってカスタム列で行う。

失敗しがちなパターンと対処法

よくある失敗例とその対策を紹介します。これらを事前に知っておくことで、無駄な作業を減らせます。

パターン1: 型変換後にエラーが多発する

特に「空文字列」や「-」などのプレースホルダーが混在している場合に発生します。先に「値の置換」でそれらをnullに変更してから型変換を行うとエラーを抑えられます。また、変換後に「エラーの置換」を使う方法もあります。この作業は、[列の追加]タブにある「条件列」を使って、特定の値だけを日付に変換するロジックを組むとより堅牢です。

パターン2: 変換後も日付として機能しない

これは主にロケール不一致が原因です。例えば、ソースデータが「2024-03-04」の場合、日本のロケールでは「2024/3/4」と解釈されますが、実際のデータが「MM-DD-YYYY」の場合は誤った日付になります。この場合は、前述の「ロケールを使用したデータ型の変更」で、データの元の形式を指定してください。具体的な指定方法は、ダイアログで「英語 (アメリカ)」を選び、日付形式を「M/d/yyyy」に設定します。

パターン3: クエリを更新すると型が元に戻る

Power Queryでは、適用したステップとして「変更の種類」が記録されていますが、ソースデータに新しい行が追加された場合、その行の値が日付として認識できないと、自動的に列全体が文字列に戻ることはありません。しかし、エラー行が発生し、後続の処理で問題が生じることがあります。この場合、エラー行を除外するか、エラーを既定値に置き換えるステップを追加します。具体的には、「エラーの置換」を使ってエラーをnullまたは特定の日付に置き換えます。

管理者に相談すべき設定

社内で共有されているデータソースや、Power Queryの管理設定が原因で問題が解決しない場合があります。以下の点を管理者に確認してください。

• Power Queryデータソースの資格情報:共有フォルダーやSharePoint上のファイルを読み込む場合、資格情報が正しく設定されていないと、最新データが取得できないことがあります。管理者にデータソース設定の確認を依頼しましょう。
• データゲートウェイの設定:オンプレミスのデータベースに接続する場合、データゲートウェイが適切に構成されていないと、日付型の変換に影響が出ることがあります。特に、日付列が文字列として渡される場合、ゲートウェイ側のデータ型マッピングが原因の可能性があります。
• Power Queryのグローバルオプション:[ファイル] > [オプション] > [データの読み込み]にある「型検出」の既定設定を変更できるかどうか、管理者に相談してください。会社全体で統一した設定にすることで、同様の問題を予防できます。

よくある質問

Q1. 列のデータ型を変更しても、Excelシートに読み込んだときに日付にならない。

Power Queryエディタでの型変更は、実際にワークシートに読み込まれるデータの型を指定しているわけではありません。Excelに読み込む際のデータ型は、[閉じて読み込む]時の設定に依存します。通常は「テーブル」として読み込まれますが、そのテーブルの列書式が「標準」になっていると、Power Queryが日付として送信しても、Excelが自動で表示形式を変更しないことがあります。解決策として、読み込み先のテーブルの列書式を直接「日付」に変更するか、Power Queryの「閉じて読み込む」の代わりに「閉じて読み込む先」を選択し、表示形式を指定します。

Q2. 日付列の中に「月-年」形式(例:Jan-2024)のデータがある。

このようなテキストは、そのままでは日付に変換できません。Power Queryでは、まず「月-年」を日付に変換するカスタム関数を作成する必要があります。例えば、Text.StartとText.Endを使って月と年を抽出し、Date.FromTextで結合する方法があります。ただし、この処理は複雑なので、別の列で「yyyy/mm/dd」形式に整形してから型変換することをおすすめします。

Q3. 毎回手動で型変換するのが面倒。自動化できないか。

クエリに適用したステップ(「変更の種類」など)は保存されるため、次回の更新時には自動的に再適用されます。ただし、新しい列が追加された場合は手動で設定する必要があります。また、同じ形式のファイルを繰り返し読み込む場合は、パラメーターを使用してクエリを汎用化することも可能です。詳細はPower Queryのヘルプをご参照ください。

Q4. 変換すると一部の日付が西暦から和暦に変わる。

これは、Power Queryが読み込んだデータをExcelに渡すときに、Excelの地域設定(和暦対応)によって表示が変わったものです。実際の値はシリアル値として保持されているので、列書式を西暦表示に変更すれば元に戻ります。Power Query側の設定では、日付型として正しく変換できていれば問題ありません。

まとめ

Power Queryで日付列が文字列として読み込まれる問題は、ソースデータの書式、Power Queryの自動検出設定、地域設定の3つの観点から切り分けると解決しやすくなります。まずはソースデータのセル書式とPower Queryエディタのデータ型アイコンを確認し、原因に応じて型変換またはロケール指定を行ってください。エラーが発生する場合は、変換前に値の置換や条件列を使って対処します。会社の共有データベースを使用している場合は、管理者にデータゲートウェイや型検出の設定を確認してもらうことも有効です。一度適切なクエリを作成すれば、以降の更新は自動的に適用されるため、作業効率が大幅に向上します。

📊

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

🏆

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