Power queryは、Excel 2016（またはそれ以降）、Excel for Office 365、Power BIに焼き付けられた非常に強力なデータ抽出および変換ツールです。

リボンの取得&データ変換セクションのデータタブにあります。

非常に強力ですがまた非常に簡単に使用でき、クエリーエディタはExcelユーザーにとって超直観的なユーザーインタフェースを持っています。 多くの変換ステップをパワー クエリ エディター リボンから簡単に実行でき、データを整理して整形するためにコードを知る必要はありません。

ユーザー フレンドリーなエディターの舞台裏では、変換プロセスの各ステップをリボン コマンドからパワー クエリー M コード言語に Excel が翻訳しています。

この記事では、パワー クエリ M コード言語の基本を紹介し、すでにパワー クエリの基本を知っていることを想定します。

目次

M コードとは何ですか?

M は data Mash-up の略で、パワー クエリはさまざまな異なるデータ ソースに接続し、それらを「マッシュアップ」するものです。

M コードはパワー クエリの舞台裏の言語です。 パワー クエリ エディター UI でデータ変換を作成するとき、Excel はクエリに対応する M コードを書いています。

M は関数型言語であり、評価および結果を返すために呼び出される関数で主に書かれていることを意味します。 M コードには、利用可能な定義済み関数の非常に大きなライブラリが付属しており、また、独自の関数を作成することもできます。

M コードの作成または編集を開始する場合、どこでそれができるかを知っておく必要があります。 数式バー

エディター UI で作成された各ステップは、数式バーで対応する M コードを表示することができます。

フォーミュラ バーが表示されない場合、[表示] タブで [フォーミュラ バー] オプションがオンになっていることを確認します。 終了したら、チェックマークをクリックするか、Enterキーを押して、変更を承認することができます。 また、X をクリックするか Esc を押して、変更を破棄することもできます。

また、数式バーの横にある fx 記号をクリックすると、数式バーでクエリのまったく新しいステップを作成することができます。

高度なエディター

数式バーは、クエリで現在選択されているステップの M コードのみを表示しますが、高度なエディターは、クエリ全体の M コードを表示および編集できる場所です。 ホーム] タブまたは [表示] タブから [詳細エディター] ボタンを押します。

“advanced” という名称にもかかわらず、このエディターは最も基本的なコード エディターで、intellisense 自動完了、構文の強調表示、または自動フォーマット機能は（まだ）ありません。

標準関数ライブラリ

M コードは関数型言語であるため、関数がすべてであり、M コードには標準ライブラリと呼ばれる定義済みの関数の大きなライブラリが付属しています。

関数の構文や例など、利用できるすべての標準ライブラリ関数に関する情報は Microsoft の Power Query M Reference Web ページで見つけることができます。

標準ライブラリは、#shared キーワードを使用してパワー クエリ エディターから検索することもできます。

数式バーに入力したら、関数名の右側の Function という文字をクリックすると、利用できるすべての関数を検索できます。

大文字/小文字の区別

M コードを書くときに最初に意識しなければならないことの 1 つは、M 言語が大文字/小文字を区別する言語であることです。 これは、あらゆる値、変数、関数などに当てはまります。

Power Queryにおける式と値

Power Queryはすべて式と値についてです。

式とは、Power Queryで値を返すために評価できるものです。 1 + 1 は値 2 と評価される式です。

値はデータの単一ピースです。 値は、数値、テキスト、論理、NULL、バイナリ、日付、時間、datetime、datetimezoneまたはdurationsなどの単一の値にすることができます。

値は、リスト、レコード、テーブルなどの単一の値より複雑な構造を持つことも可能です。 また、リスト、レコード、およびテーブルを組み合わせた値を持つこともできます。 リストのリスト、リストのテーブル、テーブルのテーブルなど、すべての可能な値の構造です。

  • “Hello World!” はテキスト値です。
  • true は論理値です。
  • null は値の不在を表します。

    • Single Intrinsic Values

    Intrinsic Value は様々な intrinsic 関数を用いて構築されます。

    • #time(hours, minutes, seconds)
    • #date(years, months, days)
    • #datetime(years, months, days, hours, minutes, seconds)
    • #datetimezone( years, months, days, hours, minutes, seconds.XXXXXXX) #datetimezone(years, months, days, hours, minute, seconds, offset-hours, offset-minutes)
    • #duration(days, hours, minutes, seconds)

    例えば、日付 2018-12-31 を構築するには、#date(2018, 12, 31) 固有関数を使って構築する必要があります。

    構造化された値

    リスト

    リストは値の順序付きシーケンスである

    中括弧を使ってリストを定義できる。 {1, 2, 3}は、1、2、3という数字を含むリストです。 順番が重要なので、これは {3, 2, 1} と同じリストではありません。

    {“Hello”, “World”} はテキスト “Hello” と “World” を含むリストです。

    リストのリストも可能で、 {{1, 2}, {3, 4, 5}} は 2 つのリストを組み合わせたリストです。 最初のリストは数字の1と2を含み、2番目のリストは数字の3、4、5を含みます。

    {x..y}という形式を使って連続したリストを作成することができます。 {2..5}はリスト{2, 3, 4, 5}を生成します。 これはテキスト文字にも使えます。 {8973>

    項目がないリストも作成でき、{} は空のリストです。

    リストは順序付けされるので、ゼロベースのインデックス番号でリスト内の項目を参照することができます。 {1, 2, 3}{2}は、これがリストの2番目のアイテムであるため、3と評価されます（ゼロインデックスに基づく）。

    レコード

    レコードは、フィールドの順序付きシーケンスです。 各フィールドは、フィールドを一意に識別するフィールド名と、任意の型の値を持つフィールド値で構成されます。

    レコードは角括弧を使って定義することができます。 は2つのフィールドを持つレコードである。 レコードの最初のフィールドは、FirstNameというフィールド名と “John “という値を持っています。

    レコードの2番目のフィールドはAgeというフィールド名と38という値を持っています。]はPersonというフィールド名とレコードであるフィールド値を持つ1つのフィールドを持つレコードです。

    空のレコードも可能で、空のレコードはis empty recordです。

    レコードのフィールド値は、そのフィールド名で参照できます。

    テーブル

    テーブルは、各行がリストである行の順序付きシーケンスです。 8973>

    #table() 関数を使用して、列見出しのリストと行のリストからテーブルを構築できます。

    #table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}}) は 2 列、3 行、列見出しが Letters と Numbers のテーブルを作成します。 #table({}, {}) は空のテーブルを生成します。

    ゼロベースの行インデックスと列見出し名でテーブル内の任意の値を参照することができます。 #table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}}){2} は、Letters 列の 2 行目（ゼロインデックスに基づく）なので、「C」と評価されます。

    式

    式は、値に対して評価できるものであれば何でも構いません。 これは値自身にも言えることです。 たとえば、式 1 は値 1 に評価されます。

    ただし、通常、式はより複雑な演算または関数で構成されるとお考えください。

  • The expression “Hello ” & “World” evaluates to “Hello World”.
  • The expression Text.Upper(“Hello World”) evaluates to “HELLO WORLD”.

    • Operators

    M コードには標準ライブラリとともに、演算子という特殊関数の一覧も存在します。 これらは正確に2つの値（または値として評価される式）を取り、1つの値を返す。

    算術

    M コードには、通常の Excel +, -, *, / から期待され慣れている基本的な算術演算子が備わっている。 これらによって、それぞれ値の加算、減算、乗算、除算ができる。

    これらは数値以外にもさまざまな種類の値に対して使用することができる。 たとえば、日付に期間を追加できます。

    #date(2018,12,25) + #duration(7, 0, 0, 0) は 2019-01-01 と評価されます。

    比較

    比較演算子 <, >, <=, >=, =, <>を使ってMコード内で値を比較することが可能です。

    • x < y は、x が y より小さければ真と評価されます。
    • x > y は、x が y よりも大きければ真と評価されます。
    • x <= yはxがy以下のとき真と評価されます。
    • x >= yはxがyより大きいか等しいとき真と評価されます。
    • x = y は x が y と等しければ真と評価されます。
    • x <> y は x が y と等しくなければ真と評価されます。

      これらは様々なタイプの値で使用することが出来ます。

      連結とマージ

      アンパーサンド & 演算子を使用して、テキストの連結やリスト、レコード、テーブルのマージを行うことができます。

      {1,2,3} & {3,4,5} は {1,2,3,3,4,5} と評価されます。

      論理

      論理値（または論理値として評価する式）に対して not, and or 演算子を使用して操作を実行することができます。

      コードのコメント

      どのプログラミング言語でも期待されるように、コードにコメントを追加することが可能です。

      1行コメント 

      M code goes hereM code goes here //This is a single line commentM code goes here

      1行コメントは、コメントの前に2つのフォワードスラッシュ文字 // を置くことで作成することができます。 8973>

      複数行コメント 

      M code goes here /*This is a commenton multiple lines*/ M code goes here

      複数行コメントは、/*と*/の間にコメントを記述することで作成されます。 これらの文字の外側にあるものはMコードとして解釈されます。

      Let文

      let文は、一連の値を評価し、変数名に割り当て、in文の後に続く式で使用することを可能にします。 各式はin文の前の最後の式を除き、カンマで区切られている。 この例では、let と in 式全体が 3.

      let c = a + b, b = 2, a = 1in c

      と評価されます。let 文の中の式は、評価される必要がある順番に表示する必要があると思うかもしれません。 しかし、そうではありません。 M コード評価器は、式の依存関係に基づいて必要な計算の順序を自動的に計算します。

      M コードが評価順序で書かれていれば、人が読みやすいのは明らかですが、もう 1 つの利点もあります。 式は、[Applied Steps] ウィンドウに個別のステップとして表示されます。 

      let a = 1, b = 2in a + b

      let… in… 式の in 部分で式を評価することもできます。

      変数名 

      let #"Is this a variable name? Wow!!!" = 1 + 1in #"Is this a variable name? Wow!!!"

      #”” を使用して式に任意の名前を割り当てることができます。 スペース文字や他の特殊文字も使用できる。 予約キーワードの使用は唯一の例外です。

      変数名はクエリーエディターの適用ステップに表示されるものなので、スペース文字を使用できるのは素晴らしい機能です。

      let Source = #table({"Numbers"}, {{1}, {2}, {3}, {4}, {5}}), #"Added Custom" = Table.AddColumn(Source, "Double", each 2*)in #"Added Custom"

      この例では、各行のNumbers列に2を掛ける新しい列を作成しています。

      let Source = #table({"Numbers"}, {{1}, {2}, {3}, {4}, {5}}), #"Added Custom" = Table.AddColumn(Source, "Double", (_) => 2*_)in #"Added Custom"

      意味的にはeachステートメントと等しいアンダースコア構文を使って同じクエリを作成することができます。

      If Then Elseステートメント

      M コードは論理式に関しては、他の言語と比較してかなり疎なものである。 select case文やloop文は用意されていません。 利用できるのは if… then.. else… 式だけです。

      if then else

      構文はまっすぐで、他のほとんどのプログラミング言語と同様です。

      Try Otherwise Statements

      エラーは、特定のタイプのデータを必要とする操作を実行しようとしたときに発生することがあります。 例えば、数値とテキスト値を掛けようとするとエラーになります。

      let Source = #table({"Number", "Number and Text"}, {{2, 2}, {2, "Hello"}}), #"Added Custom" = Table.AddColumn(Source, "Product", each try * otherwise 0)in #"Added Custom"

      エラーは try… otherwise… 式を使用することによって回避することができます。

      これにより、クエリ結果のエラーを回避し、エラーを任意の値または式に置き換えることができます。

      関数

      関数は、一連のパラメータ値から値へのマッピングです。 標準ライブラリの関数とともに、Mコードでは独自の関数を作成することができます。

      let Product = (x,y) => x * y, Result = Product(2,3)in Result

      このクエリは、2つの数値を乗算する関数を定義しています。

      Functions With Optional Parameters

      関数パラメーターには、必須パラメーターとオプションパラメーターの2つのタイプがあります。 オプションのパラメーターがない場合、関数に渡される値は NULL になります。

      let Product = (x, optional y) => if y is null then x else x * y, Result = Product(2)in Result

      この関数にはオプションの第 2 引数があります。 次に、クエリは値 2 の必須パラメータのみを使用して関数を呼び出し、評価すると 2 になります。この関数は y が null であることを考慮する必要があり、そうでなければオプションの引数が見つからない場合、関数の評価がエラーになることに注意してください。 数列の次の数は前の2つの数の和として定義されます。

      この関数は、(n-1)番目のフィボナッチ数と(n-2)番目のフィボナッチ数を足して、n番目のフィボナッチ数を見つけます。

      Excel の TRIM 関数を複製して単語間の余分なスペースを削除したり、リストに基づいて値を一括検索および置換したりする再帰関数を使用できる、より便利な例をいくつか紹介します。

      クエリ関数

      上記の例では、クエリ内で関数を定義し、クエリ内で関数を呼び出して評価しました。

      また、関数であるクエリを作成し、他のクエリから呼び出して評価することができます。 注：複数のステップを実行するには、クエリ関数の let… in… 文の中に let… in… 文が必要です。

      結論

      パワー クエリは、データのインポートと変換を自動化して簡略化できる、Excel の優れた機能です。 しかし、要件がより高度になると、作成した M コードを編集したり、ゼロから自分で書いたりする必要が出てくるかもしれません。

      どんな新しいプログラミング言語でも、まず基本を知らなければ読めません。 このガイドでは、より高度なクエリの作成を開始できるように、Mコードを使用できるようになることを期待しています。

    By: adminPosted on

    コメントを残す

    メールアドレスが公開されることはありません。