Power BI/Excelで使うM 言語の基本

By /

Power BI/Excelで使うM言語の基本：詳細解説

Power BIやExcelのPower Query（または「データの取得と変換」機能）は、様々なデータソースからデータを取得し、分析に適した形に変換するための強力なツールです。このPower Queryの裏側で動いているのが「M言語」と呼ばれる数式言語です。UI操作でデータ変換を行うと、その操作が自動的にM言語のコードとして記録されていきます。M言語を直接理解し、記述できるようになることで、Power Queryの可能性を最大限に引き出し、より複雑で柔軟なデータ変換を実現できるようになります。

この記事では、Power BIおよびExcelでデータ変換の強力な武器となるM言語（正式名称：Power Query Formula Language）の基本を詳細に解説します。約5000語のボリュームで、M言語の核となる概念から、実用的なデータ変換手法までを網羅します。

1. はじめに：M言語とは何か、なぜ学ぶのか

M言語（Power Query Formula Language）は、Power Queryエディターで使用される関数型の数式言語です。Power BI Desktop、Excel、Azure Data Factory、Power Apps dataflowsなど、Microsoftの様々なデータ統合ツールで利用されています。

UI操作でPower Queryエディターを触っていると、自動的に「適用したステップ」としてリストが表示されます。この各ステップが、M言語の1つ以上の式に対応しています。例えば、「列名の変更」というUI操作を行うと、M言語で Table.RenameColumns という関数を使った式が自動生成されます。

M言語を学ぶ主な理由は以下の通りです。

  • 複雑な変換の実現: UI操作だけでは実現が難しい、あるいは非常に手間がかかるような複雑なデータ変換をM言語で直接記述することで効率化できます。
  • 柔軟性と制御: 自動生成されたコードを理解し、編集することで、より細かな制御が可能になります。特定の条件に基づいた変換や、動的な処理などもM言語で実現できます。
  • 再利用可能な関数の作成: 頻繁に行うデータ変換パターンをカスタム関数として定義し、再利用することができます。
  • コードの理解とデバッグ: UI操作で生成されたコードを理解することで、変換処理がどのように行われているのかを把握し、問題が発生した場合の原因特定（デバッグ）が容易になります。
  • 効率的な記述: 一部の操作は、UIを何度もクリックするよりM言語で直接書いた方が速い場合があります。

M言語は、強力なデータ変換ツールであるPower Queryの中核をなす言語です。その基本を習得することは、Power BIやExcelを使ったデータ分析の幅を大きく広げることに繋がります。

2. M言語の基本概念

M言語は、式 (Expression) を評価 (Evaluate) して結果を返すという考え方に基づいています。プログラム全体は、通常1つの大きな式として表現されます。

2.1. クエリ (Query)

Power Queryエディターで作成する一連のデータ変換処理全体を「クエリ」と呼びます。各クエリは、データソースからデータを取得し、様々な変換を適用して、最終的な結果テーブルを生成します。

2.2. ステップ (Step)

クエリを構成する個々の処理単位を「ステップ」と呼びます。Power Queryエディターの右側に表示される「適用したステップ」リストがこれに該当します。各ステップは前のステップの結果を入力として受け取り、何らかの変換を施した新しい結果を出力します。これはM言語においては、前のステップの名前を変数として参照し、新しい変換式を適用するイメージです。

2.3. 式 (Expression)

M言語における最も基本的な構成要素です。式は値を返します。
例：
* 1 + 1 （数値を返す式）
* "Hello, " & "World!" （テキストを返す式）
* {1, 2, 3} （リストを返す式）
* Table.FromColumns(...) （テーブルを返す式）

2.4. let 式 (let Expression)

M言語のクエリのほとんどは、let 式と呼ばれる構造で記述されます。let 式は、一時的な変数（M言語では「識別子」と呼ばれます）を定義し、それらの変数を使って最終的な結果を計算するための構造です。

let 式の基本的な構文は以下の通りです。

m
let
VariableName1 = Expression1,
VariableName2 = Expression2,
...
ResultVariable = FinalExpression
in
ResultVariable

  • let キーワードで始まり、in キーワードで終わります。
  • let と in の間に、1つ以上の変数定義（VariableName = Expression）をコンマ区切りで記述します。これらの変数は「ステップ」に相当します。
  • 各変数定義の右辺 (Expression) は式であり、その式の評価結果が変数に代入されます。
  • 変数定義は、前に定義された変数を参照することができます。これは、前のステップの結果を受けて次のステップの処理を行う、Power Queryのパイプライン処理の考え方と一致します。
  • in の後に、最終的にそのlet式全体として返される式を記述します。通常は、最後に定義した変数の名前を指定します。

let 式の例:

“`m
let
// ステップ1: 元のデータを取得
Source = Excel.Workbook(File.Contents(“C:\Data\SalesData.xlsx”), null, true),
Sheet1_Sheet = Source{[Item=”Sheet1″,Kind=”Sheet”]}[Data],

// ステップ2: ヘッダーを昇格
PromotedHeaders = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),

// ステップ3: 列名を変更
RenamedColumns = Table.RenameColumns(PromotedHeaders,{{"Column1", "日付"}, {"Column2", "製品"}, {"Column3", "売上"}}),

// ステップ4: 売上列の型を数値に変更
ChangedType = Table.TransformColumnTypes(RenamedColumns,{{"売上", Number.Type}}),

// ステップ5: 特定の製品でフィルタリング
FilteredRows = Table.SelectRows(ChangedType, each [製品] = "A"),

// 最終結果としてFilteredRowsを返す
Result = FilteredRows

in
Result
“`

この例では、Source, Sheet1_Sheet, PromotedHeaders, RenamedColumns, ChangedType, FilteredRows, Result が変数（識別子）です。それぞれのステップが前のステップの結果を参照し、加工を加えています。最終的に FilteredRows ステップの結果が、この let 式全体の戻り値となります（ここではResultという変数に入れていますが、inの後に直接FilteredRowsと書いても同じです）。

M言語では、変数の定義順序は重要です。ある変数を定義する式の中で、まだ定義されていない変数を参照することはできません（ただし、後述するカスタム関数内など、スコープが異なる場合は例外があります）。

3. データ型 (Data Types)

M言語は静的型付けと動的型付けの両方の特性を持ちます。変数自体は明示的な型宣言なしに値を保持できますが、値にはそれぞれ特定の型があり、式の評価時には型の互換性がチェックされます。

M言語の主なデータ型は以下の通りです。

  • number: 数値型。整数、小数、無限大 (#infinity)、非数 (#nan)、負の無限大 (-#infinity) を含む。
    • 例: 100, 3.14, #infinity
  • text: 文字列型。Unicode文字のシーケンス。
    • 例: "Hello", "Power BI"
  • logical: 論理型。true または false のいずれかの値を持つ。
    • 例: true, false
  • date: 日付型。年、月、日を含む。
    • 例: #date(2023, 10, 26)
  • time: 時間型。時、分、秒を含む。
    • 例: #time(14, 30, 0)
  • datetime: 日付と時間の両方を含む型。
    • 例: #datetime(2023, 10, 26, 14, 30, 0)
  • datetimezone: 日付、時間、およびタイムゾーン情報を含む型。
    • 例: #datetimezone(2023, 10, 26, 14, 30, 0, 9, 0) (UTC+9時間)
  • duration: 期間型。時間間隔を表現する。
    • 例: #duration(0, 1, 30, 0) (0日1時間30分0秒)
  • binary: バイナリ型。ファイルのバイナリデータなどを表現する。
    • 例: File.Contents("C:\Data\image.png") の戻り値など
  • type: 型自体を表す型。型の互換性チェックや動的な型操作に使用される。
    • 例: type number, type table
  • null: 何も値を持たないことを示す特別な値。値が存在しない場合や不明な場合に使用される。
    • 例: null
  • list: 順序付けられた値のコレクション。波括弧 {} で定義され、各要素はコンマで区切る。要素は異なる型でも良い。
    • 例: {1, "テキスト", true, #date(2023, 1, 1)}
  • record: フィールド名と値のペアのコレクション。角括弧 [] で定義され、[フィールド名 = 値, ...] の形式で記述する。フィールド名はテキストである必要がある。
    • 例: [Name = "山田", Age = 30]
  • table: 列を持つ行のコレクション。M言語の多くの関数はテーブルを操作する。
    • 例: Table.FromColumns(...), #[テーブル名] (ナビゲーションで取得したテーブル)
  • function: 引数を受け取り、計算を行い、結果を返す式。カスタム関数の定義などで使用される。
    • 例: (x) => x * 2

型の互換性と変換:
異なる型の値を操作する場合、型の互換性が重要になります。例えば、数値をテキストとして扱いたい場合、Text.From() 関数などを使って明示的に型を変換する必要があります。Power QueryエディターのUIで「型の変更」を行うと、裏側では Table.TransformColumnTypes 関数が使われます。

例: Table.TransformColumnTypes(前のステップ, {{"列名", type number}})

4. 変数と識別子 (Variables and Identifiers)

M言語の let 式で定義される名前付きのコンテナを「変数」と呼びます。M言語の公式ドキュメントでは「識別子 (Identifier)」という言葉が使われます。

変数の定義方法:
let 式の中で、変数名 = 式, の形式で定義します。

m
let
MyNumber = 100,
MyText = "データ",
MyCalculatedValue = MyNumber * 2, // 前の変数MyNumberを参照
Result = MyCalculatedValue
in
Result

識別子の命名規則:
* アルファベット（大文字・小文字）、数字、アンダースコア (_) で構成されます。
* 数字で始まることはできません。
* 予約語と同じ名前は使えません。
* 大文字と小文字は区別されます (myVariable と MyVariable は別の変数です)。
* スペースや特殊文字を含む識別子を使いたい場合は、#"識別子" のように二重引用符とハッシュマーク (#) で囲む必要があります。UI操作で生成されるステップ名（例: "変更された型"）はこの形式で記述されます。

m
let
// スペースを含む変数名
#"売上データ（加工済）" = ... ,
Result = #"売上データ（加工済）"
in
Result

M言語にはいくつかの「予約語」があり、これらは変数名として使用できません (let, in, if, then, else, true, false, null, type, as, is, try, otherwise, section, shared など)。

5. オペレーター (Operators)

M言語では、値に対して演算を行うために様々なオペレーターが用意されています。

  • 算術演算子:

    • + : 加算 (数値, 日付, 時間, 日時, 期間) または リスト/テキストの連結
    • - : 減算 (数値, 日付, 時間, 日時, 期間)
    • * : 乗算 (数値, 期間)
    • / : 除算 (数値)
    • & : テキストの結合、リストの連結

    例: 1 + 2, 5 - 3, 2 * 4, 10 / 2, "Hello" & "World", {1, 2} & {3, 4}

  • 比較演算子: 論理値 (true または false) を返します。

    • = : 等しい
    • <> : 等しくない
    • < : より小さい
    • > : より大きい
    • <= : 以下
    • >= : 以上

    例: 5 = 5, 10 <> 5, 3 < 7, 10 >= 10

  • テキスト結合:

    • & : 2つのテキスト値を結合します。
    • 例: "前" & "後" は "前後" を返します。

  • リスト連結:

    • & : 2つのリストを結合します。
    • 例: {1, 2} & {3, 4} は {1, 2, 3, 4} を返します。

  • レコード/テーブルフィールドアクセス:

    • [フィールド名] : レコードから指定したフィールドの値を取得します。
      例: let myRecord = [Name="山田", Age=30] in myRecord[Age] は 30 を返します。
    • [列名] : 現在の行のテーブルから指定した列の値を取得します（行コンテキスト内でのみ有効）。これは each [列名] の形式でよく使われます。
    • [列名][行インデックス] : テーブルから指定した列の指定した行（0から始まるインデックス）の値を取得します。
      例: let myTable = #table({"ColA", "ColB"}, {{1, "A"}, {2, "B"}}) in myTable[ColA]{0} は 1 を返します。
    • {[列名=値]}[列名] : テーブルから特定の条件に一致する行を検索し、その行の指定した列の値を取得します（ユニークな一致がある場合にのみ有効）。
      例: let myTable = #table({"ID", "Name"}, {{1, "A"}, {2, "B"}}) in myTable{[ID=2]}[Name] は "B" を返します。

  • Null合体演算子:

    • ?? : 左辺の式が null でない場合はその値を、null の場合は右辺の式の値を返します。Nullチェックを簡潔に記述できます。
      例: OptionalValue ?? DefaultValue

  • 型の演算子:

    • is : 値が指定した型であるか (logical) をチェックします。
      例: 10 is number は true を返します。
    • as : 値を指定した型に変換します。変換できない場合はエラーになります。
      例: "123" as number は 123 を返しますが、"abc" as number はエラーになります。

これらのオペレーターを組み合わせて、様々な計算や条件判定を行うことができます。

6. 関数 (Functions)

M言語において、データ変換のほとんどは「関数」を呼び出すことによって行われます。関数は、特定の処理を実行し、結果を返す再利用可能なコードブロックです。

M言語の関数には、大きく分けて「組み込み関数」と「カスタム関数」があります。

6.1. 組み込み関数 (Built-in Functions / Library Functions)

M言語には、データソースへの接続、データ型の変換、テーブルの操作、リストの操作、文字列処理、日付/時間処理など、様々な用途に対応する膨大な数の組み込み関数が用意されています。これらの関数は、通常 ライブラリ名.関数名 の形式で呼び出されます。

よく使われるライブラリの例：

  • Table.*: テーブル操作に関する関数群 (Table.FromColumns, Table.TransformColumnTypes, Table.AddColumn, Table.SelectRows, Table.Join など)
  • List.*: リスト操作に関する関数群 (List.Sum, List.Average, List.Distinct, List.Transform など)
  • Text.*: テキスト操作に関する関数群 (Text.Upper, Text.Lower, Text.Trim, Text.Split, Text.Contains など)
  • Number.*: 数値操作に関する関数群 (Number.Round, Number.FromText, Number.IsOdd など)
  • Date.*, Time.*, DateTime.*, DateTimeZone.*, Duration.*: 日付と時間に関する関数群
  • Record.*: レコード操作に関する関数群
  • File.*: ファイル操作に関する関数群 (File.Contents)
  • Excel.*: Excelファイル操作に関する関数群 (Excel.Workbook)
  • Json.*: JSONデータ操作に関する関数群

組み込み関数の呼び出し方:
関数名(引数1, 引数2, ...) の形式で呼び出します。引数は関数の定義によって異なります。

よく使う組み込み関数の例:

  • Table.FromColumns(columns as list, optional columnNames as any): リストのリストからテーブルを作成します。
    m
    let
    columns = {
    {1, 2, 3}, // 1列目のデータ (リスト)
    {"A", "B", "C"} // 2列目のデータ (リスト)
    },
    columnNames = {"ID", "Value"}, // 列名のリスト
    myTable = Table.FromColumns(columns, columnNames)
    in
    myTable
  • Table.TransformColumnTypes(table as table, typeTransformations as list, optional culture as text): テーブルの指定した列のデータ型を変更します。typeTransformations は {{"列名1", 型1}, {"列名2", 型2}, ...} という形式のリストです。
    m
    let
    Source = #table({"ID", "Value"}, {{1, "100"}, {2, "200"}}),
    ChangedType = Table.TransformColumnTypes(Source,{{"Value", type number}})
    in
    ChangedType

  • Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional columnType as type): テーブルに新しい列を追加します。columnGenerator は新しい列の値を生成するための関数です。通常、現在の行を参照する each キーワードと共にラムダ関数として記述されます。
    m
    let
    Source = #table({"Product", "Price", "Quantity"}, {{"A", 100, 5}, {"B", 200, 3}}),
    AddedTotal = Table.AddColumn(Source, "合計金額", each [Price] * [Quantity])
    in
    AddedTotal
    each は、テーブルの各行に対して指定した式を評価するためのショートカット構文です。each [Price] * [Quantity] は、各行について Price 列の値と Quantity 列の値を乗算した結果を返す関数を意味します。

  • List.Sum(list as list, optional precision as number): 数値のリストの合計を計算します。
    m
    List.Sum({10, 20, 30}) // 結果: 60

  • Text.Upper(text as text): テキストを全て大文字に変換します。
    m
    Text.Upper("hello") // 結果: "HELLO"

組み込み関数は非常に多いため、全ての関数を覚える必要はありません。Power QueryエディターのUI操作を通じて自動生成されるコードを確認したり、Microsoftの公式ドキュメントを参照したりしながら、必要に応じて使い方を学ぶのが効果的です。

6.2. カスタム関数 (Custom Functions)

M言語では、独自に関数を定義することも可能です。これにより、繰り返し行う複雑な処理をモジュール化し、コードを簡潔に保ち、再利用性を高めることができます。

カスタム関数の定義方法:
カスタム関数は、ラムダ式と呼ばれる (引数リスト) => 式 の形式で定義されます。

m
(引数1 as 型, 引数2 as 型, ...) => 関数本体（引数を使った式）

定義した関数は変数に代入して使用します。

簡単なカスタム関数の例:

“`m
let
// 数値を2倍にする関数を定義
MultiplyByTwo = (x as number) => x * 2,

// 関数を呼び出す
Result = MultiplyByTwo(10)

in
Result // 結果: 20
“`

テーブル操作を行うカスタム関数の例:
よくあるデータ変換パターンを関数化する例です。例えば、指定した列の値を条件に基づいてラベル付けする関数。

“`m
let
// 売上高に応じてカテゴリを割り当てる関数を定義
AssignSalesCategory = (salesAmount as number) =>
if salesAmount >= 1000 then “高”
else if salesAmount >= 500 then “中”
else “低”,

// 元のテーブル
SourceTable = #table({"製品", "売上高"}, {{"A", 1200}, {"B", 600}, {"C", 300}}),

// カスタム関数を使って新しい列を追加
AddedCategoryColumn = Table.AddColumn(SourceTable, "売上カテゴリ", each AssignSalesCategory([売上高])),

Result = AddedCategoryColumn

in
Result
“`

この例では、AssignSalesCategory というカスタム関数を定義し、それを Table.AddColumn の columnGenerator 引数（each AssignSalesCategory([売上高]) の部分）として利用しています。each は、各行のコンテキストで AssignSalesCategory 関数を呼び出し、現在の行の [売上高] を引数として渡すことを意味します。

カスタム関数を定義することで、同じ変換ロジックを複数の場所で使い回したり、複雑な変換処理をより小さな再利用可能な部品に分割したりすることが可能になります。

7. リスト (Lists)

リストは、順序付けられた値のコレクションです。M言語においてリストは非常に基本的なデータ構造であり、多くの関数がリストを引数として受け取ったり、リストを返したりします。

リストの定義方法:
波括弧 {} を使い、要素をコンマ区切りで記述します。要素は異なる型でも構いません。

m
let
// 数値のリスト
Numbers = {1, 2, 3, 4, 5},
// 異なる型の要素を含むリスト
MixedList = {10, "Text", true, #date(2023, 10, 26)},
// リストのリスト（テーブル作成などで使用）
ListOfLists = {{1, 2}, {3, 4}}
in
ListOfLists

リストの基本操作:

  • 要素へのアクセス: 0から始まるインデックスを使ってアクセスします。
    m
    let
    MyList = {"A", "B", "C"},
    FirstElement = MyList{0}, // "A" を取得
    ThirdElement = MyList{2}, // "C" を取得
    // 範囲を指定して複数の要素を取得 (サブリスト)
    SubList = MyList{0..1} // {"A", "B"} を取得
    in
    SubList
  • 要素数の取得: List.Count() 関数を使用します。
    m
    List.Count({1, 2, 3}) // 結果: 3
  • リストの結合: & オペレーターを使用します。
    m
    {1, 2} & {3, 4} // 結果: {1, 2, 3, 4}
  • リストの生成:
    • 連続する数値を生成: List.Numbers(開始値, 要素数, optional 増分)
      例: List.Numbers(1, 5) は {1, 2, 3, 4, 5} を返します。
    • 日付のリストを生成: List.Dates(開始日, 要素数, 増分)
      例: List.Dates(#date(2023, 1, 1), 5, #duration(1, 0, 0, 0)) は5日間の日付リストを返します。
    • 他のリストから変換して新しいリストを生成: List.Transform(リスト, 変換関数)
      例: List.Transform({1, 2, 3}, each _ * 10) は {10, 20, 30} を返します。（each _ はリストの各要素を指すショートカット）

リスト関連の関数:
List. で始まる多くの関数があります。例：

  • List.Sum, List.Average, List.Min, List.Max: 集計
  • List.Sort: ソート
  • List.Distinct: 重複の除去
  • List.Select(list, 条件関数): 条件に一致する要素のみを抽出
  • List.Transform(list, 変換関数): 各要素に関数を適用して新しいリストを生成
  • List.Generate(initial as function, condition as function, next as function, optional selector as function): より複雑なリスト生成

リストは、テーブルの列を表現したり、関数に引数のリストを渡したり、集計処理を行う際に一時的なデータ構造として利用されたりと、M言語プログラミングにおいて非常に重要な役割を果たします。

8. レコード (Records)

レコードは、名前付きフィールドとそれに対応する値のコレクションです。これは他のプログラミング言語のオブジェクトやディクショナリ（辞書）に似ています。

レコードの定義方法:
角括弧 [] を使い、[フィールド名 = 値, ...] の形式で記述します。フィールド名はテキストである必要があります。

m
let
// 個人情報を表すレコード
Person = [
Name = "田中",
Age = 25,
City = "東京",
IsStudent = false
],
// ネストされたレコード
Product = [
ID = 101,
Details = [
Name = "ラップトップ",
Price = 120000,
Category = "PC"
]
]
in
Product

レコードの基本操作:

  • フィールドへのアクセス: [フィールド名] を使って値を取得します。ネストされたレコードには連続してアクセスできます。
    m
    let
    Person = [Name = "田中", Age = 25],
    ProductName = Product[Details][Name] // "ラップトップ" を取得
    in
    ProductName
  • フィールド数の取得: Record.FieldCount() 関数を使用します。
    m
    Record.FieldCount([A=1, B=2]) // 結果: 2
  • レコードのマージ/結合: Record.Merge() 関数を使用します。フィールド名が重複する場合は、後続のレコードの値が優先されます。
    m
    Record.Merge([A=1, B=2], [B=3, C=4]) // 結果: [A=1, B=3, C=4]

レコード関連の関数:
Record. で始まる関数が多数あります。例：

  • Record.Field(record, fieldName): 指定したフィールドの値を取得（エラー発生を防ぐ場合などに利用）
  • Record.AddField(record, newFieldName, newFieldValue): 新しいフィールドを追加
  • Record.RemoveFields(record, fieldsToRemove): 指定したフィールドを削除
  • Record.RenameFields(record, renames): フィールド名を変更
  • Record.FromList(list, fields): リストからレコードを作成
  • Record.ToList(record): レコードからリスト（値のみのリスト）を作成

テーブルは、レコードのリストと考えることもできます。テーブルの各行は1つのレコードに対応し、列名はレコードのフィールド名に相当します。Table.FromRecords() 関数は、レコードのリストからテーブルを作成するために使用されます。

9. テーブル (Tables)

テーブルは、M言語において最も中心的なデータ構造です。Power Queryエディターでのデータ変換のほとんどは、テーブルを操作する関数を適用することによって行われます。テーブルは、行と列から成り立ち、各列は特定のデータ型を持ちます。

テーブルの表現:
#table シンタックスを使ってインラインで簡単なテーブルを定義することもできますが、通常はデータソースからデータを取得したり、他のテーブル操作関数（例: Table.AddColumn の結果）によって生成されます。

m
let
// #table シンタックスでテーブルを定義
MyTable = #table(
{"列A", "列B"}, // 列名のリスト (ヘッダー)
{ // 行のリスト (各行は値のリスト)
{1, "X"},
{2, "Y"},
{3, "Z"}
}
)
in
MyTable

ほとんどの場合、テーブルは前のステップの結果として得られます。

m
let
Source = Csv.Document(File.Contents("C:\Data\data.csv"),[Delimiter=",", Columns=3, Encoding=65001, QuoteStyle=QuoteStyle.None]),
// Source ステップの結果はテーブル
RenamedColumns = Table.RenameColumns(Source,{{"Column1", "ID"}, {"Column2", "名前"}, {"Column3", "値"}}),
// RenamedColumns ステップの結果もテーブル
Result = RenamedColumns
in
Result

テーブル関連の関数 (Table.*)

Table. で始まる関数は非常に多岐にわたりますが、ここではデータ変換で特によく使われる関数群を紹介します。

  • データの読み込み・生成:

    • Csv.Document, Excel.Workbook, Json.Document, OData.Feed, Sql.Database など: 様々なデータソースからテーブルを取得します。
    • #table(...): インラインでテーブルを定義します。
    • Table.FromList, Table.FromColumns, Table.FromRecords: リストやレコードからテーブルを生成します。

  • 列の操作:

    • Table.SelectColumns(table, selectColumns): 特定の列を選択します。
      m
      Table.SelectColumns(前のテーブル, {"ID", "名前"})
    • Table.RemoveColumns(table, removeColumns, optional missingField): 特定の列を削除します。
      m
      Table.RemoveColumns(前のテーブル, {"不要列A", "不要列B"})
    • Table.RenameColumns(table, renames): 列名を変更します。renames は {{"古い名前1", "新しい名前1"}, ...} の形式のリストです。
      m
      Table.RenameColumns(前のテーブル, {{"Column1", "商品名"}, {"Column2", "価格"}})
    • Table.ReorderColumns(table, newColumnOrder): 列の並び順を変更します。
      m
      Table.ReorderColumns(前のテーブル, {"日付", "商品名", "価格"})
    • Table.AddColumn(table, newColumnName, columnGenerator, optional columnType): 新しい列を追加します。columnGenerator は各行に対して評価される関数（通常 each を使用）です。
      m
      Table.AddColumn(前のテーブル, "税込み価格", each [価格] * 1.10, type number)
    • Table.TransformColumns(table, transformOperations, optional defaultTypes): 指定した列の値を変換します。transformOperations は {{"列名1", 変換関数1}, {"列名2", 変換関数2}, ...} の形式のリストです。変換関数は各セルの値に対して評価される関数（通常 each を使用）です。
      m
      Table.TransformColumns(前のテーブル, {{"商品名", each Text.Upper(_), type text}, {"数量", each if _ > 10 then "大量" else "少量", type text}})
      each _ は、現在のセルの値を指すショートカットです。

  • 行の操作:

    • Table.SelectRows(table, condition): 条件に一致する行を選択します。condition は各行に対して評価される論理値を返す関数（通常 each を使用）です。
      m
      Table.SelectRows(前のテーブル, each [売上高] > 1000 and [地域] = "関東")
    • Table.Sort(table, sortOrder): 指定した列でテーブルをソートします。sortOrder は {{"列名1", 順序1}, {"列名2", 順序2}, ...} の形式のリストです。順序は Order.Ascending または Order.Descending です。
      m
      Table.Sort(前のテーブル, {{"日付", Order.Ascending}, {"売上高", Order.Descending}})
    • Table.Distinct(table, optional columns): 指定した列または全ての列について、重複する行を削除します。
      m
      Table.Distinct(前のテーブル, {"ID"}) // ID列で重複する行を削除

  • テーブルの結合・結合:

    • Table.Join(table1, key1, table2, key2, optional joinKind, optional comparer): 2つのテーブルを結合します。joinKind には JoinKind.Inner, JoinKind.LeftOuter, JoinKind.RightOuter, JoinKind.FullOuter, JoinKind.LeftAnti, JoinKind.RightAnti, JoinKind.InnerNonMatching などがあります。
      m
      Table.Join(顧客テーブル, {"顧客ID"}, 注文テーブル, {"顧客ID"}, JoinKind.LeftOuter)
    • Table.Combine({table1, table2, ...}): 複数のテーブルを行方向に結合（アペンド）します。
      m
      Table.Combine({テーブルA, テーブルB})

  • 集計・グループ化:

    • Table.Group(table, key, aggregations): 指定した列でテーブルをグループ化し、集計を行います。key はグループ化に使用する列名または列名のリストです。aggregations は集計の定義リストです。集計定義は {{"新しい列名", 集計関数, 集計対象列}, ...} の形式です。
      m
      Table.Group(前のテーブル, {"地域", "製品"}, {{"合計売上", each List.Sum([売上高]), type number}, {"件数", each Table.RowCount(_), type number}})
      each List.Sum([売上高]) は、各グループの行のリストの中から 売上高 列の値だけをリストとして取り出し、その合計を計算するという意味です。each Table.RowCount(_) は、各グループの行の数を数えます。_ はグループ化されたテーブルを表します。

  • 形状の変換 (ピボット/アンピボット):

    • Table.Pivot(table, pivotKeyColumns, attributeColumn, valueColumn, optional aggregationFunction): 指定した列のユニークな値を新しい列ヘッダーに変換します（ピボット）。
      m
      Table.Pivot(前のテーブル, {"地域"}, "製品", "売上高", List.Sum) // 地域ごとに製品を列にして売上合計をピボット
    • Table.UnpivotOtherColumns(table, pivotColumns, attributeColumn, valueColumn): 指定した列以外の列を、属性列と値列の2つの列に変換します（アンピボット）。
      m
      Table.UnpivotOtherColumns(前のテーブル, {"日付", "地域"}, "製品", "売上高") // 日付と地域以外の列をアンピボット

  • 型の変更:

    • Table.TransformColumnTypes(table, typeTransformations, optional culture): 列のデータ型を変更します（前述の通り）。

  • 値の置換:

    • Table.ReplaceValue(table, oldValue, newValue, replacer, columnsToSearch): 指定した列の値を置換します。replacer は置換方法を定義する関数（通常 Replacer.ReplaceValue または Replacer.ReplaceText）です。
      m
      Table.ReplaceValue(前のテーブル, "N/A", null, Replacer.ReplaceValue, {"売上高", "数量"}) // "N/A" を null に置換

  • Null値のフィル:

    • Table.FillDown(table, columnsToFill): 指定した列の null 値を、直上の null でない値で埋めます。
    • Table.FillUp(table, columnsToFill): 指定した列の null 値を、直下の null でない値で埋めます。

  • エラーのある行の削除:

    • Table.RemoveRowsWithErrors(table, optional columns): エラーを含む行を削除します。

これらの関数は、Power QueryエディターのUI操作で自動生成されることが多いですが、M言語で直接記述することで、より効率的かつ柔軟にデータ変換パイプラインを構築できます。

10. エラー処理 (Error Handling)

M言語では、式の評価中にエラーが発生する可能性があります（例：数値をゼロで割る、存在しないフィールドにアクセスする、不正な形式のデータを型変換しようとするなど）。エラーが発生すると、その式全体の評価が停止し、エラーが伝播します。

エラー処理には try...otherwise 式を使用します。

m
try Expression otherwise DefaultValue

  • try の後の Expression を評価します。
  • 評価が成功した場合、その結果が try...otherwise 式全体の戻り値となります。
  • 評価中にエラーが発生した場合、otherwise の後の DefaultValue が評価され、その結果が try...otherwise 式全体の戻り値となります。

エラー処理の例:
ゼロ除算によるエラーを回避する例。

“`m
let
Numerator = 10,
Denominator = 0, // ゼロ

// 通常の除算（エラーになる）
// Result_Error = Numerator / Denominator,

// エラー処理付きの除算
Result_NoError = try Numerator / Denominator otherwise 0 // ゼロで割るとエラーになるので、その場合は 0 を返す

in
Result_NoError // 結果: 0
“`

データ変換において、特定の列の値がエラーになる可能性がある場合などに、Table.TransformColumns 関数と組み合わせて使用することがよくあります。

“`m
let
Source = #table({“ID”, “Value”}, {{1, “100”}, {2, “エラー値”}, {3, “300”}}),

// Value列の型を数値に変更するが、エラーの場合は null にする
ChangedTypeWithErrorHandling = Table.TransformColumns(Source, {
    {"Value", each try Number.FromText(_) otherwise null, type nullable number}
}),

Result = ChangedTypeWithErrorHandling

in
Result
``
この例では、“エラー値”というテキストは数値に変換できないため、Number.FromText(“エラー値”)はエラーを発生させますが、try…otherwise nullによってエラーが捕捉され、null` に置き換えられます。

また、エラー自体を値として取得し、エラーメッセージや詳細を確認することも可能です。エラーが発生した結果は特殊なエラー値となり、Value.IsError()、Error.Message()、Error.Detail() といった関数でその情報を取得できます。ただし、データ変換のステップとしては、エラーを含む行を削除したり、エラー値を特定のデフォルト値に置換したりすることが一般的です。

11. 高度な概念 (簡単に触れる)

11.1. 折りたたみ (Query Folding)

M言語における非常に重要な概念です。折りたたみとは、Power Queryで行われたデータ変換処理（M言語のステップ）を、可能な限りデータソース側（データベースサーバーなど）で実行させる仕組みです。これにより、Power Queryエディターに転送されるデータ量を減らし、処理パフォーマンスを大幅に向上させることができます。

全てのM言語関数や操作が折りたたみをサポートしているわけではありません。例えば、リレーショナルデータベースからデータを取得した場合、初期のフィルタリングや列の選択といった操作はデータベース側で実行されやすいですが、メモリ内でしか処理できない複雑なカスタム関数などは折りたたまれません。

折りたたみは自動的に行われますが、Mコードを記述する際に、折りたたみ可能な関数を可能な限り早いステップで使用するように意識することで、クエリのパフォーマンスを最適化できます。

11.2. メタデータ (Metadata)

M言語の値は、値自体に加えて、追加の情報であるメタデータを持つことができます。メタデータは type [Metadata = value] の形式で表現され、値に # シンボルを使って関連付けられます。主にシステムが使用する情報（列の型情報など）や、開発者が任意に追加する情報に利用されます。

m
let
ValueWithMetadata = 10 metatype [Description = "これは重要な値です"]
in
ValueWithMetadata

メタデータは、M言語の高度な機能や特定のコネクタでのみ意識することがほとんどで、基本的なデータ変換ではあまり直接操作することはありません。

12. M言語の記述とデバッグ

M言語のコードは、Power Queryエディターの「詳細エディター」で直接記述・編集できます。

12.1. Power Queryエディターの利用

Power Queryエディターを開くと、主に以下の領域があります。

  • クエリペイン: 左側に表示され、作成中のクエリ一覧が表示されます。
  • 中央ペイン: データプレビューが表示されます。各ステップの結果を確認できます。
  • クエリ設定ペイン: 右側に表示され、「プロパティ」（クエリ名など）と「適用したステップ」リストが表示されます。

UI操作で適用したステップは、この「適用したステップ」リストに追加され、対応するM言語コードが裏側で自動生成されます。

12.2. 詳細エディター (Advanced Editor)

Power Queryエディターのリボンにある「ホーム」タブまたは「表示」タブから「詳細エディター」を開くと、現在のクエリ全体のM言語コードが表示されます。ここでコードを直接編集、新規作成、コピー＆ペーストなどを行うことができます。

詳細エディターの画面構成は基本的に let ... in ... の構造に対応しており、let と in の間に各ステップの定義が記述されています。

12.3. ステップの確認とデバッグ

Power Queryエディターの「適用したステップ」リストをクリックすることで、各ステップが完了した時点でのデータプレビューを確認できます。これはM言語のデバッグにおいて非常に重要です。どのステップで予期しない結果になったのか、エラーが発生したのかを特定するのに役立ちます。

また、詳細エディターでコードを記述する際、各ステップ（変数）の定義の最後にカーソルを置くと、そのステップの結果が一時的に表示される場合があります（特にテーブルやリストの場合）。これはコードを記述しながら中間結果を確認するのに便利です。

エラーが発生した場合は、エラーメッセージが表示されます。メッセージには、エラーが発生したステップ名や原因（型の不一致など）が含まれていることが多く、デバッグの手がかりとなります。

12.4. 変数や式の評価結果を確認する方法

詳細エディターでコードを記述中に、特定の変数や式がどのような値を返すかを確認したい場合があります。簡単な方法は、その変数名や式を選択し、右クリックして「評価」を選択するか、または詳細エディターを閉じずに右クリックして「テーブルとしてドリルダウン」などを選択することです。しかし、これは常に利用できるわけではなく、特に複雑な式の場合は難しいです。

より確実な方法は、確認したい式の結果を新しいステップ（変数）として定義し、「適用したステップ」リストでそのステップを選択して結果を確認することです。デバッグが完了したら、そのデバッグ用のステップは削除できます。

“`m
let
Source = …,
ChangedType = …,

// ★ デバッグ用ステップ ★
Debug_IntermediateResult = ChangedType,

FilteredRows = Table.SelectRows(ChangedType, each ...),
Result = FilteredRows

in
Result
``
この例では、ChangedTypeステップの結果を確認したい場合に、Debug_IntermediateResult = ChangedType` というステップを追加し、そのステップを選択してデータプレビューを確認します。

13. 具体的な使用例

M言語を使ってよくあるデータ変換シナリオを実現する例をいくつか紹介します。

例1：複数のExcelファイルを結合する

特定のフォルダ内の複数のExcelファイル（同じシート名、同じ構造）を1つのテーブルに結合するシナリオは非常に一般的です。Power QueryのUI操作で簡単に実現できますが、その裏側で動いているMコードを理解することは、より柔軟な応用（特定のファイルのみ含める/除外する、ファイル名やパスを列として追加するなど）のために役立ちます。

“`m
let
// 1. 対象フォルダのファイル一覧を取得
Source = Folder.Files(“C:\Data\MonthlyReports”),

// 2. Excelファイル (.xlsx) のみを選択
ExcelFiles = Table.SelectRows(Source, each Text.EndsWith([Name], ".xlsx")),

// 3. 各Excelファイルからデータを抽出する関数を定義
//    (UIで「ファイルの結合」を行うと、この部分の関数が自動生成される)
//    ここでは簡略化のため、各ファイルの最初のシートのデータ全体を取得
GetDataFromExcel = (FilePath) =>
    let
        Source = Excel.Workbook(File.Contents(FilePath), null, true),
        // 仮に最初のシートのデータを使うと想定
        SheetData = Source{0}[Data]
    in
        SheetData,

// 4. 各ファイルパスに関数を適用して、データのリストを作成
//    Add column with the file data
AddData = Table.AddColumn(ExcelFiles, "FileData", each GetDataFromExcel([Content])), // [Content]はバイナリデータ、GetDataFromExcelでファイルパスではなくバイナリを受け取るように修正

// 正しくは [Content] 列（バイナリ）を使って Excel.Workbook を呼び出す
// Refactored: Function to get data from an Excel binary content
GetDataFromExcelBinary = (BinaryContent) =>
    let
        Source = Excel.Workbook(BinaryContent, null, true),
        // 仮に最初のシートのデータを使うと想定
        SheetData = Source{0}[Data]
    in
        SheetData,

// Apply the refactored function
AddData = Table.AddColumn(ExcelFiles, "FileData", each GetDataFromExcelBinary([Content])),

// 5. 各ファイルデータのテーブルにファイル名などの情報を追加（オプション）
//    Add the file path information to each inner table
AddFileInfo = Table.TransformColumns(AddData, {"FileData", each Table.AddColumn(_, "ファイル名", (row) => [Name], type text)}, null, MissingField.Ignore),

// 6. 各ファイルデータのテーブルのリストを結合する
CombinedData = Table.Combine(AddFileInfo[FileData]),

// 7. 列名を調整（必要に応じて）
//    UIでヘッダー昇格や列名変更を行うとここにステップが追加される
PromotedHeaders = Table.PromoteHeaders(CombinedData, [PromoteAllScalars=true]),
// ... (列名の変更や型の変更など)

Result = PromotedHeaders // 例としてヘッダー昇格まで

in
Result
``
この例は、UIで「フォルダから」→「結合と変換」を実行した際に生成されるコードを簡略化したものです。ポイントは、フォルダ内のファイル一覧を取得し、各ファイルのコンテンツ ([Content]列) に対してデータを抽出する関数を適用し、得られたテーブルのリストをTable.Combine` で結合している点です。

例2：条件に基づいて新しい列を追加する

既存の列の値に基づいて、条件付きで新しい列を作成する例です。

“`m
let
Source = #table({“商品”, “売上”}, {{“A”, 50}, {“B”, 120}, {“C”, 80}, {“D”, 200}}),

// if 式を使って売上カテゴリ列を追加
AddedCategory = Table.AddColumn(Source, "売上カテゴリ", each
    if [売上] >= 100 then "高"
    else if [売上] >= 70 then "中"
    else "低"
),

Result = AddedCategory

in
Result
``if … then … else …はM言語の条件分岐構文です。eachと組み合わせて各行の[売上]` の値に対して条件を評価し、新しい列の値を決定しています。

例3：テキスト列を分割する

コンマや区切り文字で区切られたテキスト列を複数の列に分割する例です。

“`m
let
Source = #table({“ID”, “Tags”}, {{1, “A,B,C”}, {2, “X,Y”}, {3, “P”}}),

// Tags列をコンマで分割し、リストとして新しい列に追加
AddedSplitList = Table.AddColumn(Source, "Tags_List", each Text.Split([Tags], ",")),

// リストを必要に応じて展開する (UIで「新しい行に展開」など)
// 例えば、各タグを個別の行にする場合
ExpandedTags = Table.ExpandListColumn(AddedSplitList, "Tags_List"),

Result = ExpandedTags // 結果: IDとタグが各行に展開されたテーブル

in
Result
``Text.Split()関数はテキストを指定した区切り文字で分割し、リストを返します。Table.ExpandListColumn()` 関数は、リストを含む列をリストの各要素ごとに新しい行に展開します。

14. M言語を学ぶためのリソース

  • Microsoft Learn ドキュメント (Power Query M function reference): M言語の関数リファレンスとして最も公式で網羅的な情報源です。各関数の説明、引数、使用例が掲載されています。
    https://learn.microsoft.com/en-us/powerquery-m/
  • Microsoft Power BI Community / Excel Community: M言語に関する質問や情報交換ができるフォーラムです。他のユーザーのコード例や問題解決方法を参考にできます。
  • 各種ブログやオンラインコース: M言語に特化したチュートリアルや記事、動画などがインターネット上に多数存在します。「Power Query M language tutorial」などのキーワードで検索してみてください。
  • 書籍: M言語やPower Queryに特化した書籍も出版されています。

これらのリソースを活用し、実際にPower Queryエディターで様々なUI操作を行い、生成されるMコードを確認しながら学ぶのが効果的な学習方法です。

15. まとめ

この記事では、Power BIおよびExcelのPower Queryで使用されるM言語の基本的な概念、データ型、オペレーター、関数（組み込み関数とカスタム関数）、リスト、レコード、テーブルといった主要な要素について詳細に解説しました。また、let 式の構造、エラー処理、コードの記述とデバッグ方法、具体的な使用例についても触れました。

M言語は関数型の言語であり、式の評価によってデータ変換のステップを積み重ねていくという独特の考え方を持っています。最初はUI操作で自動生成されるコードを読み解くことから始め、徐々にカスタムコードの記述や編集に慣れていくのが良いでしょう。

M言語を習得することで、Power Queryの適用範囲と柔軟性が格段に向上します。定型的なデータ変換作業の自動化、複雑なデータソースからの情報抽出、再利用可能な変換ロジックの構築など、データ分析の前処理において強力な武器となります。

この記事が、M言語の学習を始める皆さんにとって、その基本を理解し、更なる学習を進めるための足がかりとなれば幸いです。Power Queryエディターを開き、実際にコードに触れることから始めてみてください。

コメントする

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

上部へスクロール