Power BI (Power Query)で列をマージする方法 (なぜマージが必要なのか)




この記事ではPower BI上で異なるテーブルの列データをマージする方法について紹介していきます。マトリックステーブルを作成した際に、各行は正しい値が出力されているが、合計の値がおかしいといった問題に直面したことはありませんか?

この記事を読むことで以下の疑問を解消できます。

・どうやってマージするのか?

・そもそもなぜ列をマージする必要があるのか?

・マージしなくても問題ないのではないか?

それでは以下で説明していきます。

結論

・Power Query側で紐づけたいデータを指定してマージすることで異なるテーブルから列を追加できる。

・一つのテーブルにデータをまとめることでマスタテーブルに必要な情報を後から追加できる。

・単純なDAXのSUMでは複数のテーブルを経由した間接的なリレーション同士を正しく集計できないため、マージか仮のテーブルを作成する必要がある。

・マージ以外の解決法はVAR関数とSUMX関数を組み合わせたDAXを作る

今回は以下の販売実績のテーブルとマスタテーブルを例にして説明していきます。

DAXで行の集計は合うが、合計が合わない問題

・まずPower Queryを開きます。

・販売実績は製品マスタと単価コード列でリレーション、単価コードも製品マスタと単価コード列でリレーションを作成します。

・ここで次のDAXを作成します。

・このDAXと製品マスタを使ってマトリックステーブルを作ると以下のようになります。

このように各行の計算結果は正しいのですが合計の値がSUM販売数の合計と単価の合計の掛け算になってしまいます。

計算ミスの原因は上図のように単価のテーブルと販売数のテーブルに直接的なリレーションが存在しないこと、SUM関数で集計したDAX同士を掛け合わせていることです。

この問題を解決するには、

1.製品マスタに単価列をマージする

2.VAR関数で仮のテーブルを作り、SUMX関数で集計する

の二つの方法が挙げられます。

Power Queryで列のマージを行う方法

上記のレポートでPower Queryを開き、クエリのマージをクリック

製品マスタと単価コードのテーブルを単価コード列を基準にしてOKを選択

製品マスタのテーブルに単価コードテーブルが追加されます。

単価コードテーブル列の右上をクリックして単価コード列のみを製品マスタに追加します。

これで単価列のマージは完了です。

これで販売数も製品マスタに追加されました。

最後に販売数*単価の列を追加してテーブルに反映させれば完了です。

このように一つのテーブルにまとめることで同じ行の異なる列の集計が可能です。

VAR関数で仮のテーブルを作り、SUMX関数で集計する方法

こちらはDAXを一つ作成することで完了です。

以下のDAXを作ります。

各関数の簡単な説明ですが、

VAR…名前付きの変数として式の結果を格納できる (VAR…RETURNで閉める)

SUMMARIZE (テーブル名, 列1, “名前”, 式…)…基準となるテーブルを指定してそのテーブルの列と作成したDAXメジャー、参照元のテーブル (相手がマスタ)の列を一つのテーブルにまとめることができる

SUMX (テーブル名, 式)…指定したテーブルの列の合計を求める。SUMと異なり、同じ行の異なる列の四則演算が可能

先ほどのクエリのマージを行った式と同様の結果が得られました。初めのうちはクエリのマージの方が単純ですが、行数の大きいデータを扱う場合、列を複数追加するとその分データの読み込みに時間がかかってしまいます。

VARでDAX上に仮のテーブルを作成することで余分な時間の削減も可能です。また、作成されたPower BIレポートを基に別のレポートを作成する場合、後から既存のテーブルに列を追加することができませんのでこちらの方法でしか、解決できない場面も出てくるかと思います。ぜひ使いこなしていきましょう!

まとめ

クエリのマージ方法となぜマージが必要か理解できましたでしょうか。

Power BIはExcelと比較して横方向の集計に癖があります。そのため、集計を行う際は一つのテーブルにまとめる、またはDAX上で一つのテーブルを作成し集計を行いましょう!

Power BIはまだまだ日本語の情報が少ないですが、Shiminlogでは基礎的な情報から応用的な内容まで記事を追加していきます。ぜひ他の記事もご覧ください。




コメントを残す

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