【PowerQuery】形がバラバラのDBを１つのテーブルにまとめる2

先日の続き

前回の最終形

今回の最終形

出力比というキー１つに対して出力が2つあるのでキー目線にまとめ直した。

また、キーを数値にすると呼び出す側でFORECAST.LINEAR(線形補間)がかけられるようにもなる。

呼び出す側の関数がこんなにスッキリ

検索列

=[@機種名]&"_"&
[@検索列1]&"_"&
[@検索列2]&"_"&
TEXT(VALUE([@出力比]),"0.00")

A,W列

=IFERROR(
  ROUND(
    FORECAST.LINEAR(

      TEXT(VALUE([@出力比]),"#.##"),

      OFFSET(テーブル名,
        MATCH([@検索列],テーブル名検索列,-1)-1,
        6,
        2,
        1),

       OFFSET(テーブル名,
        MATCH([@検索列],テーブル名検索列,-1)-1,
        4,
        2,
        1)
      ),
  1),
"")

出力比を1行にまとめて検索列で一意に決まるようにクエリ側を整形してあげることで呼び出す側の関数の負荷がめちゃくちゃ楽になる。だからデータベースファーストなのだ。

あとは適宜クエリの列数に合わせてOFFSET内の列数のところを調整するだけでいい。

1つ目のOFFSETはA,W列

2つ目のOFFSETは出力比列

比較用：クエリで整形する前のクロス表を参照する関数

=LET(Val_v_検索値_,[@v],
     Val_x検索値_,[@x],

     Area_x転置_,SORT(TRANSPOSE(Area_x)),
     Area_y,INDIRECT($C$1 & "_" & $D$1),

     Count_x_,COUNT(Area_x),
     Pos_xMatch_,MATCH(Val_x検索値_,Area_x転置_,1),
     Pos_vMatch_,MATCH(Val_v_検索値_,Area_v,0)-1,

     Area_x_既知,OFFSET(Area_x,0,Count_x_-Pos_xMatch_,1,2),
     Area_y_既知,OFFSET(Area_y,Pos_vMatch_,Count_x_-Pos_xMatch_,1,2),

     Ans_y_,FORECAST.LINEAR(Val_x検索値_,Area_y_既知,Area_x_既知),
Ans_y_)

大量の名前付き範囲とINDIRECT、``LET関数を使っててこの複雑さ...
いかにデータベース側を正規化しておくかが大事だと知れた好事例。

注意点

この例のFORECAST.LINEAR(線形補間)部分は参照先のテーブル降順である必要があるためマクロで常に降順にしておくサポートが必要。

おわりに

少しずつパワークエリがわかってきた気がする。
そろそろクエリのユーザー定義関数化とか使い回しの術を身に着けたい。