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の動作がキーであることを前提に動作するようになるということ。一意の行を特定するためのキーとして使用した時に評価されるものだ。
Table.Keys(
Table.AddKey(
Table.FromColumns(
{
{1, 2, 2, 4, 5} // 値が重複している
}
),
{"Column1"}, true
)
) = {
[Columns = {"Column1"}, Primary = true]
} // true
マークした列に含まれる値に重複があってもクエリの評価結果はerrorにならない。
一意の行を特定するヒントとして
キーが定義されていないテーブルで、
// 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)を定義したテーブルで
// SourceQuery
Table.AddKey(
#table(
2,
{
{"List1", {1,2,3}},
{"List2", {4,5,6}}
}
),
{"Column1"}, true
)
| Column1 | Column2 |
|---|---|
| List1 | [List] |
| List2 | [List] 👈ココ |
2 行目の Column2 の list をドリルダウン
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 列(ファイル名) でユニークになるデータソース。
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) で特定する。
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.Keys(
Table.Group(
#table(3, {}),
{"Column1", "Column2"}, {}
)
) = {
[Columns = {"Column1", "Column2"}, Primary = true]
} // true
Table.Keys(
Table.Distinct(
#table(3, {})
)
) = {
[Columns = {"Column1", "Column2", "Column3"}, Primary = true]
}
なので、Table.Addkey で独自にキーを用意しなければならないというケースは少ない。
思ったこと🙄
Power Query での集計パフォーマンスを改善する内容をポストしたのだけど、どうしても Table.AddKey を使わなければならないということではないし、キーを定義したからといって評価パフォーマンスが常に良くなるということでもない。パフォーマンスが低下している原因を特定したうえでの対応策だってことだ。
その他