Power QueryはExcelで使われる関数と同じように、全ての処理は関数が関わってきます。これらの関数はM言語と呼ばれ、UIを使って実行するとExcelのマクロのように自動的に記録されていきます。Power Queryの初心者であればM言語を特に気にする必要はないですが、データ処理をより高度に効率化させるためにはPower Queryの関数について知っておく必要があります。
今回はこのPower Queryで使用される関数について、個別紹介ではなく、最も基本的な操作等について紹介したいと思います。
Power Queryの関数とは
Power QueryはデータのETL*1を行う機能で、ExcelとPower BIの両方に搭載されています。この機能はデータ・モデリング*2やDAX*3を用いて、データ分析を可能にするための機能であり、Excelの一般的なユーザーから上級者に親しまれています。
Power Queryの関数は以下のようなものがあり、いずれもUIを操作した際に自動的に記述されるものとなっています。
例1: =Folder.Files("C:\Users\〇〇\PQ\SampleFiles\CSV")
CSVフォルダの直下に入っているファイルを全て抽出
この例では、Folder.Filesという記述がPower Queryの関数であり、カッコ内がフォルダパスとなっています。
例2: = Table.TransformColumns(Data, {"年月", each Date.From(_ & "01"), type date})
列[年月]のテキスト型を日付型へ変更
例2でも青字のコマンドがPower Queryの関数であり、Excelのネスト関数（関数の中に関数を入れる）と同じく、関数同士を組み合わせることで様々な操作が可能になります。
このように、Power Queryでは関数を使って操作を行っていくわけですが、Excelの関数と異なるのは、Excelの関数は1つの値（スカラー値）を算出するところ、Power Queryは殆どの場合、データ分析ができるようなテーブルを最終アウトプットとして表示させることになります。そのため、上記例2の処理が終わっても同じような操作が続き、その都度新しい関数を使って、新しいステップが生成されるわけです。
Power Query関数レファレンス①
Power Queryの関数一覧を表示させる方法はいくつかありますが、ExcelやPower BIのクエリエディタから調べる方法を記載しておきます。以下、図説しながら手順を紹介していきます。
Excelの場合
- Excelを立ち上げる
- タブ「データ」>「データの取得」>「その他のデータソースから」>「空のクエリ」
- クエリエディタを開き、数式バーに=#sharedと入力し、リストをテーブル化
- 各種関数を確認
- Folder.Filesを選択 > [Value]列にあるFunctionの隣の空白をクリックし、概要説明を確認
このように、 =#sharedを空白のクエリから作ることで、関数のレファレンスを作ることができるようになります
Power BIの場合
Power BIから関数を参照する手順はExcelと同じ手順になります。以下の通り、「ホーム」>「データを取得」>「空のクエリ」の順にクリックしていき、=#sharedを入力すれば関数のレファレンスを作ることができます。
Power Query関数レファレンス②
①のように、レファレンスを作ることが1つの方法ですが、オンライン上でも多くの参考資料が落ちていますので、こちらもぜひ参考にしてみてください。
公式レファレンス
Power Query M 関数参照 - PowerQuery M | Microsoft Docs
- 良いところ
a) 新しい関数が追加されると公式サイトも更新される
b) 全て読めばPower Queryに関する知識がかなり身につく - 悪いところ
a) 必要以上に細かい解説まで入っており、読むのに時間がかかってしまう
b) 英語を日本語に翻訳したものになるため、ニュアンスが伝わりにくい場合も
M言語のルール
M言語にはいくつかルールが存在しており、全てを把握するのは難しいですが、以下代表的なものを紹介しておきます。
公式サイトによる紹介
Power Query M 数式言語の概要 - PowerQuery M | Microsoft Docs
公式サイトで紹介されているルールを可能な限り理解することがベストですが、書き手がプログラマーなのか、非常に分かりにくい表現が多いです。従って、下記「基本的に知っておくべきルール」を押さえておくほうが無難だと思いますので、簡単に紹介しておきます。
M言語について知っておくべきルール
- M言語の由来はアルファベット順に決められたもので、特に意味はない模様
- M言語はPower Query用に開発された言語であり、SQL*4に似た性質を持つ
- クエリを操作していくことで、M言語が自動的に記録されていき、マクロの自動記録のような性質を持つ
- 下記letスタートのソースコードはPower Queryで「フォルダの選択」>「ファイルの選択」を処理したものですが、主な意味は下記の通り。
let
ソース = Folder.Files("C:\Users\user\Desktop\PQ\"),
フィルターされた行 = Table.SelectRows(ソース, each ([Name] = "ABC.xls") )
in
フィルターされた行
- ソースコードはクエリエディタの「表示」>「詳細エディター」より確認可能
- ソースとフィルターされた行は「適用したステップ名」（自分で分かりやすい名称に変更可能）
- =の後の処理が「ステップ名」に代入される（=は代入を意味する）
-
構文はletで始まり、inで締めくくる（全て小文字）
-
2番目のステップ（フィルターされた行）でTable.SelectRowsは必ず前ステップのソースを参照する必要があり、その次（inの後の締めくくり）も同様
-
ステップはカンマ「,」で区切る必要があるが、最後のステップ（inの前のステップ=フィルターされた行）にはカンマを記入しない
-
Mの関数Folder.FilesとTable.SelectRowsは必ず大文字で始まり、ピリオド「.」で繋ぎ、また大文字で始まる（Folder.filesやfolder.Filesではエラーが発生=M言語は大文字・小文字を区別する=ケースセンシティブ）
- Folder.Files内の"C:\..."はテキスト扱いになるため、必ずダブルクォーテーション" "で括る
- 列を参照する場合は[ ]で括る（[Name], [Content], [Extension]等）
- eachは[Name]列の各行に対する処理を行うために必要なステートメント
このように、データソースの接続（ソース）とファイルの選択（フィルターされた行）という2ステップの操作の背後には、上記のコードが自動的に記録されているわけです。初心者はここまで見る必要はないですが、下記に述べるユーザー別でみると、中～上級者（ビジネスアナリスト以上）はこの程度は理解しておく必要があります。
ユーザー別M言語に対する必要なレベル
ユーザー別にM言語を習得するレベルは変わってきます。ここでは、大まかに下記3タイプ別に見ていきたいと思います。
- ビジネスユーザー
M言語は知らないが、UI操作で簡単なデータ集計ができるレベル - ビジネスアナリスト
UIで生成されたステップにエラーが発生した場合、そのエラーの特定や修正ができるレベル - BIプロフェッショナル / ITシステム担当
M言語を熟知しており、BIダッシュボードに最適なクエリを常に意識しながらM言語を手動で書けるレベル
まとめ
今回はPower Queryの関数言語であるMの基礎について紹介しました。正直なところ、Mは最初のうち覚える必要はないですが、構文let～inやその他ルールをある程度覚えておくと、慣れてきたときにエラーチェックができるようになります。
また、ステップ別に自動生成されたM言語（Folder.FilesやTable.SelectRows等）を眺めることで自然とその関数について覚えるようになりますので、=#sharedを活用しながら勉強していくことでPower Queryに関するスキルの上達に繋がると思います。
