上級者向け Power Query リスト基本と応用

Power Query は、その ほとんどの機能 を マウス操作 で利用できます。

しかし、プログラミングでしか使えない機能もいくつかあります。

この記事ではそんな Power Query の隠れ機能的 なもののうち、リスト についてご紹介します。

リスト を使いこなすと高度な処理ができるようになりますので、上級を目指す方は是非勉強して習得することをオススメします。

そうは言っても、リストは比較的簡単な概念 ですので、学習のハードルは低いです。この記事を流し読みするだけでもある程度理解できてしまうと思いますので興味のある方は是非ご一読下さい。

では、早速いってみましょう！

リストとは

リストとは複数の値が入ったもの で、例えば { 1, 2, 3 } と3つの数値が入ったものもリストになります。

リストを表現する場合、値を 中括弧 ‘{ }’ で囲い 、値同士の間は カンマ , で区切って 表現します。

中に入る値は任意のデータ型（数値、文字列、日付、その他のリスト、レコードなど）を含むことができます。

Power Query エディターには リストを処理するボタンがほとんど無い ので、マウス操作では手が出せません。

しかし、リストを処理するM関数は多数用意 されています。

ですから、M関数でプログラミングすることでリストの様々な処理が可能です。

リストの作り方

リストを処理するにしても最初にリストを作らないと始まりませんので、まず最初にリストの作り方を４パターンご紹介します。

コードに直接記入

コードに自分で直接複数の値を列記する方法になります。

Power Query 詳細エディタで下記コードを記入します。

let
    listA = { 1, 2, 3}
in
    listA

すると図のようにリストが自動でテーブルに変換されてExcel シートに出力されます。

以下の解説では let 〜 in の部分は省略して本体部分だけ記載しますのでご注意ください。

書式を使って生成

リスト生成のための書式がありますので紹介します。

ListA = {1..100}

上記のコードで 1 から 100 までの整数の数列が作成されます。

リスト生成関数 List.Dates

続いては、リスト生成関数を使う方法です。

Calendar = List.Dates(#date(2023, 1, 1), 365, #duration(1, 0, 0, 0))

このコードで 2023年1月1日 から 2023年12月31日 までの連続した日付のリストを作成できます。

何かのカレンダーデータを作成する際に便利です。

クエリの列を変換

クエリ出力の列はリストと同じように複数の値を持ちます。しかしそのままではリストではないので、これをリストに変換する必要があります。

例えば 「ソース」というクエリ があって、その 「商品名」という列 をリストに変換したい場合は、図のように記載します。

ListA = ソース[商品名]

これによりListA という変数にはリストが格納されます。

リストの操作（List関数）

リストの操作には多くのM関数が揃っています。Microsoftの公式サイトに全ての関数の解説が載っています。

List 関数 - PowerQuery M

詳細については、以下をご覧ください。List 関数

公式サイトの説明は簡潔過ぎて多くの方にとっては分かりにくいと思うので、ここでは幾つかの関数を図解も入れて解説させて頂きます。

【選択】List.FirstN：リストの始めからN個取り出す　

選択関数 はリストの中から指定のものを取り出す関数です。

List.FirstN 関数 はリストの始めからN個の要素を取り出す関数です。

List.FirstN({ 1, 2, 3, 4, 5, 6}, 3)

【変換関数】List.Transform：各要素に式を実行　

変換関数 はリストに何らかの処理を加える関数です。

List.Transform 関数 は、リストの各要素に処理を加えて新たなリストとして出力します。

List.Transform({"a","b"}, each "@" & _ & "@")

【メンバーシップ関数】List.PositionOf：検索値の位置を返す

メンバーシップ関数 はリストの全要素を検査した結果を返す関数です。

List.PositionOf 関数 はリストの中を検索して指定した値の位置を返します。

List.PositionOf({"aa","bb","cc","dd","ee"}, "cc")

【操作の設定】List.Union：リストの和集合

操作の設定 は数学における集合の処理をします。高校時代に ベン図 という丸が重なった図を習った記憶がある方もおられるかもしれません。これが集合の概念です。

List.Union 関数 は複数のリスト（＝集合）の和集合を出力します。

List.Union(
{{“a”,“b”,“c”}, {“b”,“c”,“d”}, {"c","d","a"}})

【順序】List.MaxN：大きいものから順にN個出力

順序付け関数 はリストの各要素を大きさの順にソートしてその順から要素を出力する関数です。

List.MaxN 関数 は大きいものから順に指定した個数の要素を出力します。

List.MaxN({ 1, 7, 5, 3, 6, 5, 8}, 3)

List.MaxN({ 1, 7, 5, 3, -6, 5, -8}, 3, 
                    (x) => Number.Abs(x))

【平均値】List.Average：リストの平均値

平均値 の関数は平均などリストの統計処理を行います。

List.Average 関数 はリストの平均値を計算します。

List.Average({1,2,3,4,5,6})

【加算】List.Sum：リストの合計

ExcelのSum関数と同じく リストの数値を合計する関数です。

List.Sum({1,2,3,4,5,6})

List関数以外の関数

リストを操作する関数は List.### 形式のList関数だけではありません。

Text.Combine：文字列結合

リストの要素を全て一列に結合して一つの文字列にする関数です。2つめの引数で結合する文字列の間に入れる文字列を指定することができます。

Text.Combine({“A”,”B",“C"},”-”)

Table.FromList：リストからテーブル作成

作成したリストを今度はテーブルに変換する関数です。２つ目の引数で文字列を分割する方法を指定することができます。

リスト処理の応用例

リストを使いこなせると様々な処理が可能になります。そこで応用例を紹介します。

累計の計算

累計とは例えば毎月の売上額のデータがあった場合、毎月の売上を順に合計していき、累積額を計算 するものです。

年間の売上目標に対して毎月どこまで売上が伸びているかを評価したい場合などに有効です。

その他、口座の入出金の情報があった場合にはそれらを全て集計して、各時点における口座残高を計算する場合 も累計計算を行います。

Power Query エディターには累計計算をするGUIボタンは無いので、自分で M関数式の編集も交えての操作が必要 です。

その方法を順に解説します。

処理の概要

累計の計算は上述の売上の例であれば月ごとにデータがソートされている必要があります。

なのでまず、累計計算する評価軸でソートされたデータ（クエリ）を準備します。

その上で累計計算することを考えると、1行目は最初なので 1行目の値がそのまま累計値 です。

2行目を考えてみましょう。2行目の累計値は 1行目の値と2行目の値の合計 が累計値になります。

3行目は同様に、1, 2, 3行目の値の合計値 となります。

つまり、以下のように行が進むごとに合計する範囲が大きくなるように計算すれば良いことになります。

この計算をPower Query で実現すれば良い訳です。

まとめると次のステップで処理することになります。

評価軸（年月など）でクエリをソート
1から始まるインデックス列を追加
インデックスを使って、各行でインデックスの示す行までの合計を計算

では、続いて具体的な処理をみていきます。

評価軸でソート

図のように月ごとの売上データがあった場合、以下の手順で月でソートします。

月の列の 三角マークのボタン をクリック
昇順で並べ替え を押す

インデックスを追加

次の手順で1から始まるインデックス列を追加します。

Power Query エディターのメニューから 列の追加 をクリック
インデックス列 をクリック
1から をクリック

M関数で計算列を作成

前述の計算が出来る式をM関数で作成します。

Power Query エディターのメニューから
列の追加 をクリック
カスタム列 をクリック
以下のM関数式を記入

List.Sum(
    List.FirstN(
        追加されたインデックス[売上],
        [インデックス]
    )
)

インデックス列の削除

累計値の計算が終わったらインデックス列は不要ですので、列を選択した後で DELキー で削除しておきます。

累計処理まとめ

以上の操作で 月ごとの売上累計額 が計算できました。

リストの概念を理解できていれば比較的簡単な処理でできることがお分かりいただけたかと思います。

累計はお金の管理などで良く使われますので、活用の場面は多いと思いますので、是非マスターしておきましょう。

グループ化で備考欄などの文字列を結合

データをグループ集計する操作は Power Query エディターの操作で比較的簡単にできます。

しかし、通常では 備考欄などの文字情報 はグループ化できません。

ところが、M関数を上手く活用することで、図のように文字情報もグループ化が可能です。

ここでは文字情報のグループ化の方法を解説します。

処理の概要

この処理は以下のたった2ステップで実現できてしまいます。

Power Query エディターで グループ集計 する
Power Query エディターで M関数式を追加編集

早速手順の詳細をみていきます。

グループ集計する

上の図のような売上のデータを 年月ごとにグループ化 する操作を解説します。

テーブルなどからデータの読み込みが終わっている状態から下記の操作をします。

Power Query エディターでグループ化する 年月の列を選択
Power Query エディターのメニューから 変換 ⇒ グループ化 をクリック
出てきたウィンドウで図のように設定

数式欄でM関数式を編集

ここで、Power Query エディターで自動で生成された M関数式を編集 して備考欄のテキストを追加します。

グループ化は Table.Group 関数 で実行されます。この関数の中身を追記します。

Table.Group 関数 はグループ集計処理の列を複数作成することができます。ですので、備考欄テキストを結合した列を追加する訳です。

図のようにM関数を編集します。

 ,{"備考", each Text.Combine([備考], "#(lf)")}

ここで、文字列の間に入れる文字として #(lf) を指定しています。これは改行を意味する 特殊文字 です。
これを入れることで、セルの中で改行させて表示することができます。

文字情報のグループ化まとめ

以上の操作で、グループ化で数値のみならず文字情報まで集計して表示することができました。

グループ集計でも備考欄の情報も欲しいケースがありますが Power Query エディターの標準機能では実現できません。

このやり方を覚えておけば備考欄の情報も欠落することなくまとめることができます。

まとめ

この記事では Power Query のリストについて基本と実際の活用事例をご紹介しました。

これらは Power Query エディターのGUI操作では使えない機能ですが、習得しておけば更に応用が効きます。

より高いレベルを目指される方は是非マスターしてその他の活用も考えてみて下さい。