Power Query のテーブルにキーを定義することができる。Power Query Online であれば、[Mark as key / キーとしてマーク] というコマンドが用意されているけれども、デスクトップ環境では用意されていないのです。いずれにせよ、定義に含まれる列名のリストの値もしくはその組み合わせがユニークであることを保証するものではない。対象の列をキーとしてマークするだけ。

Table.AddKey でテーブルにキーを定義すればクエリの評価が常によくなるということではない。

Adds a key to table, where columns is the list of column names that define the key, and isPrimary specifies whether the key is primary.
table にキーを追加します。なお、columns はキーを定義する列名のリストで、isPrimary はキーが主キーかどうかを指定します。

ユニークであることを保証していない

対象の列、もしくは、その組み合わせに含まれる値がユニークであることを保証するものではないし、定義したとき検証が実施されるわけではない。後続する Power Queryの動作がキーであることを前提に動作するようになるということ。一意の行を特定するためのキーとして使用した時に評価されるものだ。

Power Query

Table.Keys(
    Table.AddKey(
        Table.FromColumns(
            {
                {1, 2, 2, 4, 5} // 値が重複している
            }
        ),
        {"Column1"}, true
    )
) = {
    [Columns = {"Column1"}, Primary = true]
}   // true

マークした列に含まれる値に重複があってもクエリの評価結果はerrorにならない。

一意の行を特定するヒントとして

キーが定義されていないテーブルで、

Power Query

// SourceQuery
#table(
    2,
    {
        {"List1", {1,2,3}},
        {"List2", {4,5,6}}
    }
)

Column1	Column2
List1	[List]
List2	[List] 👈ココ

2 行目の Column2 の list をドリルダウン

// Query (1)
let
    Source = SourceQuery,
    Column2 = Source{1}[Column2]    // index による Item access
in
    Column2

index で指定する Item access になる。

キー(列:Column1)を定義したテーブルで

Power Query

// SourceQuery
Table.AddKey(
    #table(
        2,
        {
            {"List1", {1,2,3}},
            {"List2", {4,5,6}}
        }
    ),
    {"Column1"}, true
)

Column1	Column2
List1	[List]
List2	[List] 👈ココ

2 行目の Column2 の list をドリルダウン

Power Query

let
    Source = SourceQuery,
    List2 = Source{[Column1="List2"]}[Column2]
in
    List2

定義したキーを利用する Item access になる。

Source{[Column1="List2"]} という Item access で一意の行が特定できない場合は、そもそも Expression.Error である。定義されたキーは、一意の行を特定するためのヒントとして利用されるという理解。

すでに定義されている

Power Query エディタによるナビゲーション
特定の Power Query 関数

で用意されたり利用されている。いくつか挙げてみる。

Folder.Files
Folder Path 列(ファイルのロケーション) と Name 列(ファイル名) でユニークになるデータソース。

Folder.Files (Power Query)

Table.Keys(
    Folder.Files(FolderPath)
) = {
    [Columns = {"Folder Path", "Name"}, Primary = true]
}    // true

Content 列でドリルダウンすると、Source{[#"Folder Path"="<folder path>",Name="<file name>"]} という Item access になる。

SharePoint.Table
SharePoint リストはリスト名(表示名)ではなく Id(GUID) で特定する。

SharePoint.Tables (Power Query)

Table.Keys(
    SharePoint.Tables(
        SPSiteRoot,
        [Implementation="2.0", ViewMode="All"]
    )
) = {
    [Columns = {"Id"}, Primary = true]
}    // true

Items 列でドリルダウンすると、Source{[Id="<SPList_GUID>"]}という Item access になる。なので、SharePoint リストの表示名を変更したとしても、該当する SharePoint リストを参照し続けることができる。

Table.Group / Table.Distinct
行のグループ化で利用する列や行の重複を削除するときにキーが用意/利用されていて、それらの結果からキーを参照することができる。

Table.Group (Power Query)

Table.Keys(
    Table.Group(
        #table(3, {}),
        {"Column1", "Column2"}, {}
    )
) = {
    [Columns = {"Column1", "Column2"}, Primary = true]
}    // true

Table.Distinct (Power Query)

Table.Keys(
    Table.Distinct(
        #table(3, {})
    )
)  = {
    [Columns = {"Column1", "Column2", "Column3"}, Primary = true]
}

なので、Table.Addkey で独自にキーを用意しなければならないというケースは少ない。

思ったこと🙄

Power Query での集計パフォーマンスを改善する内容をポストしたのだけど、どうしても Table.AddKey を使わなければならないということではないし、キーを定義したからといって評価パフォーマンスが常に良くなるということでもない。パフォーマンスが低下している原因を特定したうえでの対応策だってことだ。

その他