見出し画像

Power BI-vlookup的なことをするために

tentomushi

以前はExcel を使った仕事が多かった僕にとっては、
・Excel関数のVLOOKUPやHLOOKUP、INDEXとMATCHの組み合わせ
・配列関数(CES数式)
・vbaでのfindやIFのネストソートしていく的なこと
をPower BI desktopでやろうとすると、頭がExcelあたまになっているので、少し戸惑うことが多い。

そこで、Power BI desktop上でVLOOKUP的なことをするための方法として、どんなパターンがあるのか、今思いつく分だけまとめておくことにした。

1. Power BIでのVlookup的な手段（一覧）

今時点で思いつくPower BI desktopでのLookup的なことは、以下です。

クエリエディタ（M関数）では、

1. IF - THEN - ELSE でディシジョンツリー的に条件分岐させる
2. 詳細エディタで複数条件を指定していく
3. Keyでひっかけて、クエリをマージし、展開する

データ画面（DAX関数）では、

4. LOOKUP関数を使用する
5. IF - THEN - ELSE でディシジョンツリー的に条件分岐させる
6.CALCULATE()の中で、FILTER()しまくる

※もっとちゃんと整理したらあると思うのですが、また思いついたら追加します

2. やりたいことのイメージ

やりたいことは、あるデータテーブルに含まれている情報をKeyとして、別のテーブルからデータを引っ張ってくることですが、
言葉で書いてもいまいちなので、例を作ってみました。（テーブルを2個例として作ってみました）

前提1：割引率テーブル
：1カ月間の総仕入数量が一定数量（MOQ)に達したら、それぞれの商品に応じて、各仕入先から一定の仕入割引が受けられる。
（最初から割引後の金額で仕入れるのではなく、1カ月間の仕入が全て終わった時点で、割引額が決定されるという前提）

画像1

前提2：仕入実績テーブル
：日々の仕入情報が記録されていくテーブル

画像2


やりたいことは、この「仕入実績テーブル」を使って、今月の割引率を計算したいので、ひとまず割引率テーブルから「割引率」と「MOQ」を列として引っ張ってくること。
具体的にはこんなイメージ。

画像3

ちなみに、実際ちゃんとした会社であれば「商品番号」に一意な値を付けているだろうから、今回の例題のように「複数条件※」の検索を行う必要はないと思う。
（※ 「商品番号」が仕入先ごとに振られており、全体では重複しているため、「仕入先」と「商品番号」の組み合わせで、商品を特定しなければならない)

3.サンプルデータの取り込み

詳細エディタはM関数でもDAX関数でも用意されていないことができる。（Excelでいうと、Excel関数でもCSE数式でもできないことはマクロを使う、みたいなイメージだと僕は思っています。）

とりあえず、Power BI desktopを立ち上げて、サンプルデータを取り込んで、実際に詳細エディタで編集可能な状態に持っていきたいと思います。

Power BI desktopを立ち上げて・・・

画像4


2つのテーブル（「割引率テーブル」と「仕入実績テーブル」）を取り込んで、クエリエディタ画面を出しておきます。

画像5


4.「詳細エディタ」で複数条件の抽出を行う

では早速、「仕入実績テーブル」の詳細エディタを使って、「割引率テーブル」から複数条件を使って必要な情報を抽出したいと思います

編集前の詳細エディタの記述はこうなっています

//編集前の詳細エディタの記載内容

let
   ソース = Excel.Workbook(File.Contents("C:\Users\Desktop\note用\20200512_PowerBIでVLOOKUP的なことをしたいとき\vlookup的なことをしたいとき.xlsx"), null, true),
   仕入実績テーブル_Table = ソース{[Item="仕入実績テーブル",Kind="Table"]}[Data],
   変更された型 = Table.TransformColumnTypes(仕入実績テーブル_Table,{{"販売日", type date}, {"仕入先", type text}, {"商品番号", Int64.Type}, {"標準仕入単価", Int64.Type}, {"仕入数量", Int64.Type}})
in
   変更された型

Step1: 適当にカスタム列を追加しておく
「詳細エディタ」で、自分で記述する量を減らすために、できるだけ自動で記述されていく機能を有効活用したい。今回は列を追加する作業を行うので、クエリエディタ画面でカスタム列を追加しておき、その自動で記述されたM言語を利用する。

クエリエディタ画面＞列の追加＞カスタム列　をクリックして、

画像6

まずは「割引率」を「仕入実績テーブル」に追加したいので、列名もあらかじめ「割引率」としておきます。
そして、カスタム列は「＝[商品番号]」としておきます（これは、「割引率」を抽出するための引数として「商品番号」を使うことが予め自明のため、あえて「商品番号」を選んでいます）

画像7

OKを押して、「カスタム列」の追加が終わったら、再び「詳細エディタ」へ移動します。
詳細エディタの記述はこのように自動的に変わっています。

//カスタム列を追加した後の詳細エディタの記述

let
   ソース = Excel.Workbook(File.Contents("C:\Users\Desktop\note用\20200512_PowerBIでVLOOKUP的なことをしたいとき\vlookup的なことをしたいとき.xlsx"), null, true),
   仕入実績テーブル_Table = ソース{[Item="仕入実績テーブル",Kind="Table"]}[Data],
   変更された型 = Table.TransformColumnTypes(仕入実績テーブル_Table,{{"販売日", type date}, {"仕入先", type text}, {"商品番号", Int64.Type}, {"標準仕入単価", Int64.Type}, {"仕入数量", Int64.Type}}),
   追加されたカスタム = Table.AddColumn(変更された型, "割引率", each [商品番号])
in
   追加されたカスタム

この一文が追加されましたね、

追加されたカスタム = Table.AddColumn(変更された型, "割引率", each [商品番号])

詳細エディタを使って（VLOOKUP的に）他のテーブル（ここでは「割引率テーブル」）から値を引っ張ってくるためには、この一文を以下のように編集します。

追加されたカスタム = Table.AddColumn(変更された型, "割引率", each 割引率テーブル{[商品番号=_[商品番号],仕入先=_[仕入先]]}[割引率])

このように「追加されたカスタム」の一文を修正して「完了」をクリックすると、

画像8

でました！「割引率」です。

でも、ひとつ不安があると思います。

もし「割引率テーブル」に該当する割引率が登録されていなかったら・・・

これはErrorになってしまいます。なので、エラーを回避するための工夫をしておく必要があります。

先ほどの「追加されたカスタム」の一文は、もし該当する割引率が登録されていなかった場合に、「0」と返すようにしてみます。

   追加されたカスタム = Table.AddColumn(変更された型, "割引率", each try 割引率テーブル{[商品番号=_[商品番号],仕入先=_[仕入先]]}[割引率] otherwise 0

eachの後ろに try ~ otherwiseの構文を入れて、該当がなかった場合に0を返すという内容に修正しました。


あとは、「MOQ」についても、上記と同様の方法で引っ張ってくることができます。


参考に、M言語のリファレンスも一つ載せておきます。
ここに、try ~ otherwiseの構文が紹介されています。

以上になります。

いいなと思ったら応援しよう！