【Excel】Power Queryの「詳細エディター」活用!ExcelのM言語を直接編集する入門ガイド

ExcelのPower Queryは、データの取得、変換、整形を自動化できる強力なツールです。

しかし、GUI操作だけでは実現できない複雑な処理も存在します。

そのような場合に役立つのが「詳細エディター」です。

この記事では、Power QueryのM言語を直接編集する方法を初心者向けに解説します。

Power QueryのM言語とは

Power Queryは、その裏側で「M言語」と呼ばれる独自の関数型言語を使用しています。

GUI操作でデータ変換を行う際、Power Queryはその操作をM言語のコードとして自動生成しています。

詳細エディターを使うことで、このM言語コードを直接確認・編集できます。

詳細エディターの開き方

Power Queryエディターを開いた状態で、リボンの「ホーム」タブにある「詳細エディター」ボタンをクリックすると、詳細エディターウィンドウが表示されます。

または、「クエリ」ペインで編集したいクエリを右クリックし、「詳細エディターの表示」を選択しても開けます。

1. Power Queryエディターを開く
   Excelの「データ」タブから「データの取得と変換」グループにある「テーブルまたは範囲から」などを選択します。
2. 詳細エディターボタンをクリック
   Power Queryエディターのリボンメニュー「ホーム」タブにある「詳細エディター」をクリックします。

M言語の基本構文

M言語は、ステップ(処理の単位)の連なりで構成されています。

各ステップは「変数名 = 式」という形式で記述され、カンマ(,)で区切られます。

最後のステップの結果が、クエリの最終的な出力となります。

ステップの記述方法

各ステップは、前のステップの結果を参照して処理を進めます。

例えば、「前のステップ」という名前のステップの結果を使いたい場合は、「次のステップ = Table.AddColumn(“前のステップ”, “新しい列名”, each [既存の列名] * 2)」のように記述します。

このように、ステップの名前を適切に付けることが重要です。

データ型

M言語では、数値、テキスト、日付、真偽値(True/False)など、様々なデータ型が扱われます。

データ型を正しく指定しないと、意図した計算や処理が行われない場合があります。

例えば、数値として扱いたいのにテキスト型になっている場合は、明示的に型変換を行う必要があります。

M言語の編集と適用

詳細エディターでM言語コードを編集した後は、「完了」ボタンをクリックして変更を適用します。

編集内容に構文エラーがある場合、Excelはエラーメッセージを表示します。

エラーメッセージを参考に、コードを修正してください。

コードの編集例

既存のステップに新しい処理を追加したり、不要なステップを削除したりできます。

例えば、列の名前を変更したい場合は、「Table.RenameColumns」関数を使用します。

コードの特定の部分をコピー&ペーストして、処理を複製することも可能です。

変更の適用

詳細エディターウィンドウの右下にある「完了」ボタンをクリックすると、編集したM言語コードが実行されます。

Power Queryエディターのプレビュー画面に変更が反映されているか確認してください。

問題がなければ、「閉じて読み込む」をクリックしてExcelシートに結果を表示させます。

よく使うM関数

M言語には、データ操作のための様々な関数が用意されています。

ここでは、特に利用頻度の高い関数をいくつか紹介します。

Table.AddColumn

既存のテーブルに新しい列を追加する関数です。

計算結果や条件分岐の結果などを新しい列として追加したい場合に使用します。

構文例: `Table.AddColumn(前のステップ, “新しい列名”, each [既存の列名] * 2, Int64.Type)`

Table.RenameColumns

テーブルの列名を変更する関数です。

GUI操作でも可能ですが、複数の列名を一度に変更したい場合に便利です。

構文例: `Table.RenameColumns(前のステップ, {“古い列名”, “新しい列名”})`

Table.SelectRows

条件に一致する行のみを抽出する関数です。

特定の条件を満たすデータだけを絞り込みたい場合に使用します。

構文例: `Table.SelectRows(前のステップ, each [列名] > 100)`

Table.Group

指定した列の値に基づいてテーブルをグループ化し、集計を行う関数です。

例: 地域ごとの売上合計などを計算する場合に使用します。

構文例: `Table.Group(前のステップ, {“地域”}, {{“合計売上”, each List.Sum([売上]), type number}})`

Text.Combine

複数のテキスト文字列を結合する関数です。

例えば、姓と名を結合して氏名を作成する際などに使用します。

構文例: `Text.Combine({[姓], ” “, [名]})`

Date.Year, Date.Month, Date.Day

日付データから年、月、日をそれぞれ抽出する関数です。

日付を分解して分析したい場合や、特定の期間でデータを集計したい場合などに役立ちます。

構文例: `Date.Year([日付列])`

詳細エディター利用時の注意点

構文エラーへの対処

M言語の構文は厳密です。

括弧の閉じ忘れ、カンマの欠落、スペルミスなどが原因でエラーが発生しやすいため、注意深くコードを確認する必要があります。

エラーメッセージが表示されたら、どの行でどのようなエラーが発生しているかを確認し、該当箇所を修正してください。

ステップの依存関係

M言語の各ステップは、前のステップの結果に依存しています。

ステップの順序を間違えたり、前のステップでエラーが発生していると、後続のステップも正しく実行されません。

ステップを削除・追加・順序変更する際は、この依存関係を意識することが重要です。

GUI操作との連携

詳細エディターでM言語を直接編集した場合でも、GUI操作で追加されたステップはそのまま残ります。

GUI操作とM言語の直接編集を組み合わせることで、より柔軟かつ効率的にデータ変換を行えます。

ただし、GUI操作で自動生成されるコードと手動で記述したコードが意図せず干渉しないよう、注意が必要です。

バージョンによる違い

Power Queryの機能は、ExcelのバージョンやMicrosoft 365の更新によって進化しています。

新しいM関数が追加されたり、既存の関数の仕様が変更される可能性があります。

参照しているドキュメントやヘルプ情報が、使用しているExcelのバージョンに対応しているか確認することが推奨されます。

Power QueryとM言語の応用

カスタム列の作成

詳細エディターを使えば、GUI操作だけでは難しい複雑な計算ロジックを持つカスタム列を作成できます。

例えば、複数の列の値を組み合わせた文字列を作成したり、条件によって異なる数値を算出したりすることが可能です。

`Table.AddColumn`関数と、条件分岐(`if … then … else …`)を組み合わせることで、高度なカスタム列が作成できます。

エラー処理の追加

データソースによっては、予期せぬ値や欠損値が含まれることがあります。

このような場合に備えて、M言語でエラー処理を組み込むことができます。

`try … otherwise …`構文を使用すると、エラーが発生しても処理を継続し、代替値を返すといった制御が可能です。

クエリの結合と追加

複数のクエリを結合(Merge)したり、縦に連結(Append)したりする操作も、M言語でより細かく制御できます。

例えば、結合する際のキー列の指定や、照合順序のカスタマイズなど、GUI操作では選択肢が限られる場合でも、M言語を使えば柔軟に対応できます。

`Table.NestedJoin`や`Table.Combine`といった関数が、これらの操作に利用されます。

カスタム関数(ユーザー定義関数)の作成

繰り返し使用する一連のデータ変換処理を、M言語でカスタム関数として定義できます。

これにより、コードの再利用性が高まり、クエリの可読性も向上します。

詳細エディターで新しいクエリを作成し、関数定義を記述することで、他のクエリから呼び出して利用できるようになります。

まとめ

Power Queryの詳細エディターを使えば、M言語を直接編集して高度なデータ変換が可能になります。

M言語の基本構文とよく使う関数を理解することで、GUI操作だけでは実現できない複雑な処理を実装できます。

構文エラーやステップの依存関係に注意しながら、ぜひ詳細エディターでのM言語編集を試してみてください。

カスタム列の作成やエラー処理の追加など、Power Queryの可能性が大きく広がります。

📊

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

🏆

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