PowerBI Desktopでテーブル操作関数の使い方をまとめる

2023.02.25

本サイトは広告収入およびアフィリエイト収益を受けております。

スポンサーリンク
スポンサーリンク

お疲れ様です。きざきまるおです。

今回はPowerBI Desktopのテーブル操作関数の使い方についてまとめていこうと思います。
PowerBIは便利な反面、制約も多く、取り込んだテーブルをそのまま使えるということも多くはないので、新しいテーブルを別途作る必要があります。
そんなときに使えるのがテーブル操作関数なのでぜひ、覚えていってください。

それではどうぞ。

テーブル操作関数とは

新しいテーブルを作り出すか、既存のテーブルを操作して新たなテーブルを作り出します。
DAX関数でテーブルを指定する場面でこちらのテーブル操作関数を使って作り出したテーブルを指定するなんて場面でもよく使います。

今回のサンプルコードではこちらで作成したデータを利用しますので、ぜひ参照してみてください。

ADDCOLUMNS

指定されたテーブルに列を追加します。
追加列には計算式を使えるため拡張性の高い列を作成できます。

addcolumins = ADDCOLUMNS('テーブル', "追加列名", 計算式...)

サンプルコード

総額からdiscountの値段を減算してどれくらい割引されたかを算出して新たなテーブルを作成します。
新しいテーブルを作成してテーブル式を入力しましょう

addcolumns = ADDCOLUMNS('orders', "総額-discount", [総額] - [総額_discount])

はい。というわけでちゃんと追加した列が反映されていますね。
ADDCOLUMNSは複数列指定することが出来るので、他のテーブルの値と計算させる場合とかでも使えそうですね。

公式マニュアルは以下になります。
https://learn.microsoft.com/ja-jp/dax/addcolumns-function-dax

CROSSJOIN

２つ以上のテーブルの直積した結果を返します。
マスタ用のテーブルを作成するときに使用します。

crossjoin = CROSSJOIN('テーブル', 'テーブル'...)

サンプルコード

検証用に以下テーブルを作成しましょう。
DATATABLE関数を使うことで簡易的なテーブルを作成することが出来ます。

datatable = 
DATATABLE(
    "sample", STRING,
    {
        {"A"},
        {"B"},
        {"C"}
    }
)

そして、作成したテーブルとproductテーブルをCROSSJOIN関数で直積してみましょう。

crossjoin = CROSSJOIN('product', 'datatable')

はい。直積のテーブルが出来上がりましたね。
SQLを触ったことがある人なら直積なんて避けるべきだ！と思う人も多いかと思いますが、レポートを作成するうえでマスタの存在は非常に重要なのでPowerBIではちょこちょこ使用するケースがあります。

公式マニュアルは以下になります。
https://learn.microsoft.com/ja-jp/dax/crossjoin-function-dax

DISTINCT

指定テーブルから重複する行を削除して新たなテーブルを返します。
DISTINCT関数にはテーブルを受け取るパターンと列を受け取るパターンがありますが、新たにテーブルを作成するのはテーブルを受け取るパターンで列を受け取るパターンは条件として使用されます。

そのため、今回はテーブルを受け取るパターンを紹介します。

distinct = DISTINCT('テーブル')

サンプルコード

DISTINCT関数の動きを確認するために以下テーブルを作成します。

datatable = 
DATATABLE(
    "num", INTEGER,
    "string", STRING,
    "boolean", BOOLEAN,
    {
        {1, "A", True},
        {1, "A", True},
        {2, "A", True},
        {2, "A", False},
        {2, "B", True},
        {2, "B", True},
        {2, "B", False}
    }
)

DISTINCT関数でテーブルを作成しましょう。

distinct = DISTINCT('datatable')

はい。ということですべての行が一致した場合は行数が１つになっていますね。

公式マニュアルは以下になります。
https://learn.microsoft.com/ja-jp/dax/distinct-table-function-dax

EXCEPT

左側のテーブルと右側のテーブルを比較し、左側のテーブルに存在して右側のテーブルに存在しない行を返します。なお、２つのテーブルは同じ列数で同じ順序でないと効力を発揮しません。

except = EXCEPT('テーブル', 'テーブル')

サンプルコード

動作を確認するために以下２つのテーブルを作成しましょう。

datatable1 = 
DATATABLE(
    "num", INTEGER,
    "string", STRING,
    {
        {1, "A"},
        {1, "B"},
        {1, "C"},
        {2, "A"},
        {2, "B"},
        {2, "C"}
    }
)
datatable2 = 
DATATABLE(
    "num", INTEGER,
    "string", STRING,
    {
        {1, "A"},
        {1, "B"},
        {1, "C"}
    }
)

さて、EXCEPT関数を使ってみましょう。

except = EXCEPT('datatable1', 'datatable2')

はい。datatable2に存在しなかったnumが2の行がすべて抜き出されていますね。
あるテーブルからFILTERで行を切り取られなかった行を操作したいというときによく使います。

公式マニュアルは以下になります。
https://learn.microsoft.com/ja-jp/dax/except-function-dax

INTERSECT

指定した２つのテーブルの共通行を取得し、テーブルとして返します。

intersect = INTERSECT('テーブル', 'テーブル')

サンプルコード

確認用のデータはEXCEPT関数で作成したテーブルを使用しましょう。

intersect = INTERSECT('datatable1', 'datatable2')

はい。EXCEPTと逆の結果になりましたね。
こちらは２つのテーブルの共通部分を探したいときに使用します。

公式マニュアルは以下になります。
https://learn.microsoft.com/ja-jp/dax/intersect-function-dax

GROUPBY

指定された列でグルーピングし、グルーピングごとに計算した結果をテーブルとして返します。

groupby = GROUPBY('テーブル', 列名, "集計列名", 集計関数)

サンプルコード

ordersテーブルを商品名でグルーピングして総額を合計します。

groupby = 
GROUPBY(
    'orders',
    'orders'[商品名],
    "総額集計",
    SUMX(CURRENTGROUP(),'orders'[総額])
)

結果としてはグルーピングに指定した列とグループごとの集計がされていますね。
SQLの記述は違うものの、GROUPBYと動きは似ていますので、SQLを触る人は理解がしやすいのではないでしょうか？

公式マニュアルは以下になります。
https://learn.microsoft.com/ja-jp/dax/groupby-function-dax

NATURALINNERJOIN

２つのテーブルを自然結合します。
共通の列が存在しないとエラーになるので注意してください。

naturalinnerjoin = NATURALINNERJOIN('テーブル', 'テーブル')

サンプルコード

どうやら結合する列で同じ名前の列が存在するとエラーになるみたいなので、product_diccountテーブルの商品名列を「商品名_nj」へ変更します。

ordersテーブルとproduct_discountテーブルを自然結合します。
商品名列が共通列となっているので、そちらを軸に結合することになります。

naturalinnerjoin = NATURALINNERJOIN('orders', 'product_discount')

はい。ちゃんと結合されていますね。
SQLの自然結合とは若干挙動が異なるので列名の扱いには注意してください。

公式マニュアルは以下になります。
https://learn.microsoft.com/ja-jp/dax/naturalinnerjoin-function-dax

NATURALLEFTOUTERJOIN

左側のテーブルを軸として外部結合をします。
SQLのLEFT OUTER JOINと同じ動きになります。

naturalleftouterjoin = NATURALLEFTOUTERJOIN('テーブル', 'テーブル')

サンプルコード

NATURALINNERJOINと同じテーブルを使用します。
左側には’orders’テーブルを置くことにします。

naturalleftouterjoin = NATURALLEFTOUTERJOIN('orders', 'product_discount')

はい。ordersテーブルを軸にしているので全行表示されていますね。
加えて、product_discountテーブルでordersテーブルに存在しない商品名の部分は空白となって表示されています。
こちらはPowerBI上で新しいテーブルを作って作業する際によく使うので覚えておくと便利です。

公式マニュアルは以下になります。
https://learn.microsoft.com/ja-jp/dax/naturalleftouterjoin-function-dax

UNION

２つのテーブルの和集合テーブル（縦に結合）を作成します。ただし、列数は２つのテーブルで同一でないといけません。
日付ごとに分割されたテーブルをくっつけるときによく使います。

union = UNION('テーブル', 'テーブル'...)

サンプルコード

UNION関数の動作を確認するために以下テーブルを作成します。
ordersテーブルにFILTER条件を付けて作成したテーブルになります。

filter_union = 
FILTER(
    'orders',
    [総額] < 100000
)

全部で640件がヒットします。
ではUNIONテーブルで和集合のテーブルを作成しましょう。

union = UNION('orders', 'filter_union')

はい。元のテーブル999件と先ほどのテーブル640件を合わせて1639件のテーブルが作成されました。
PowerBIはその特性上、日付ごと、月ごとでテーブルを分けることが多いのでUNIONでテーブルを縦に結合する機会はとても多くなると思います。

公式マニュアルは以下になります。
https://learn.microsoft.com/ja-jp/dax/union-function-dax

VALUES

テーブルを指定した場合は行を返し、列を指定した場合は指定列のみのテーブルを返します。
スライサーで複数の値を選択したときにその複数の値を抽出して条件に指定するなどの幅広い使用方法があります。

values = VALUES('テーブル' or [列名])

サンプルコード

今回はスライサーを複数選択した場合の挙動を確認します。
条件文のIN VALUES(‘orders'[商品名])が該当します。

calculate_values = 
CALCULATE(
    SUM('orders'[総額]),
    'orders'[商品名] IN VALUES('orders'[商品名])
)

はい。選択した２つの商品のみ合計されていますね。
なぜこのような動作になるかというと、VALUES関数で指定した列のテーブルを作成後、スライサーにてテーブルの情報が絞られるという内部仕様によってこんな動作になります。
こちらはリレーションを張ることが出来ないテーブルの条件を抽出したいときによく使う手法なので覚えておいて損はないと思います。

公式マニュアルは以下になります。
https://learn.microsoft.com/ja-jp/dax/values-function-dax

テーブル関数のまとめは以上になります。
PowerBIでは欲しい情報を得るために新しいテーブルに情報を加工することがよくあるので、ぜひマスターしましょう。

それではまた。

タイトルとURLをコピーしました