タイトルは単に勢いです。でも、知っているとちょっと役立ちます。
Power BI Desktop や Excel などの クエリ エディター で集計に不要な列(column)除外していくことは重要なタスクです。クエリの列の削除操作として、
- 列の削除
- 他の列の削除
があります。似ている感じはしますけど、それぞれ適した使い方があるので試しながら整理。
操作と得られる結果
列の削除
- 不要な列を選択し削除する。
Table.RemoveColumns(
table as table, // 対象のテーブル
columns as any, // 対象の列名(文字列) 複数の列が対象のときは list で
optional missingField as nullable MissingField.Type
) as table
Table.RemoveColumns - Power Query M function reference
他の列の削除
- 選択した列以外を削除する。
- 列を選択(Table.SelectColumns) していることがポイント。
Table.SelectColumns(
table as table, // 対象のテーブル
columns as any, // 対象の列名(文字列) 複数の列が対象のときは list で
optional missingField as nullable MissingField.Type
) as table
Table.SelectColumns - Power Query M function reference
例で確認
"Query1_a" というクエリ(type table)があって、
| col1 | col2 | col3 |
|---|---|---|
| a | 10 | 削除する列の1 |
| b | 15 | 削除する列の2 |
// Query1_a
let
Source = #table(
type table [col1 = text, col2 = number, col3 = text],
{
{"a", 10, "削除する列の1"}
,{"b", 15, "削除する列の2"}
}
)
in
Source
クエリ "Query1_a" を参照するクエリを2種類作成。この時点ではそれぞれが参照しているにすぎないのでいずれも同じ結果になる
// 列の削除
let
Source = Query1_a
in
Source
//他の列の削除
let
Source = Query1_a
in
Source
それぞれ適用してみる
列 "col1"と"col2" が残るようにステップを追加する。
// 列の削除
let
Source = Query1_a,
// 列 "col3" を選択して、[列の削除]
#"Removed Columns" = Table.RemoveColumns(Source,{"col3"})
in
#"Removed Columns"
// 他の列の削除
let
Source = Query1_a,
// 列 "col1" と "col2" を選択して、[他の列の削除]
#"Removed Other Columns" = Table.SelectColumns(Source,{"col1", "col2"})
in
#"Removed Other Columns"
| col1 | col2 |
|---|---|
| a | 10 |
| b | 15 |
この時点でも同じ結果を得られる。
列が追加されたとき
クエリ "Query1_a" にはない列も持つクエリ "Query1_b" を用意し、
| col1 | col2 | col3 | col4 |
|---|---|---|---|
| c | 20 | 削除する列の3 | 追加される列の1 |
| d | 25 | 削除する列の4 | 追加される列の2 |
// Query1_b
let
Source = #table(
type table [col1 = text, col2 = number, col3 = text, col4 = text],
{
{"c", 20, "削除する列の3", "追加される列の1"}
,{"d", 25, "削除する列の4", "追加される列の2"}
}
)
in
Source
クエリ "Query1_a" に追加(Append) する。
// Query1_a
let
Source = #table(
type table [col1 = text, col2 = number, col3 = text],
{
{"a", 10, "削除する列の1"}
,{"b", 15, "削除する列の2"}
}
),
#"Appended Query" = Table.Combine({Source, Query1_b})
in
#"Appended Query"
| col1 | col2 | col3 | col4 |
|---|---|---|---|
| a | 10 | 削除する列の1 | null |
| b | 15 | 削除する列の2 | null |
| c | 20 | 削除する列の3 | 追加される列の1 |
| d | 25 | 削除する列の4 | 追加される列の2 |
Table.Combine により、行が追加されると同時に 列 "col4" が追加された状態になる。
その結果どうなるか
参照しているクエリの行および列が追加されているので、それぞれのクエリの結果も反映されていることを確認。
列の削除
- 行は追加される。
- 参照元で追加された列も追加される。(削除対象になっていないということ)
| col1 | col2 | col4 |
|---|---|---|
| a | 10 | null |
| b | 15 | null |
| c | 20 | 追加される列の1 |
| d | 25 | 追加される列の2 |
他の列の削除
- 行は追加される。
- 参照元で追加された列は追加されない。
| col1 | col2 |
|---|---|
| a | 10 |
| b | 15 |
| c | 20 |
| d | 25 |
該当列が存在しないとき(MissingField)
では、引数 columns で指定した列名が存在しなかったときどうするかには、 引数 missingField を指定し対応すればよい。
| MissingField.Type | 値 | RemoveColumns | SelectColumns |
|---|---|---|---|
| MissingField.Error(既定値) | 0 | Expression.Error | Expression.Error |
| MissingField.Ignore | 1 | 無視される | 無視される |
| MissingField.UseNull | 2 | - | 列が追加される |
MissingField.Error(既定値)
- 引数 missingField の既定値。
- 対象となる列がひとつでも存在しないとき、クエリは Expression.Error になり 結果(type table)を返さない。
MissingField.Ignore
- 存在しない列を無視した結果(type table)が得られる。
MissingField.UseNull
[他の列の削除] を使用しているとき、存在しない列 "colX" と MissingField.UseNull を指定すると、値 null の列として 列 "colX" が追加された結果(type table) が得られる
let
Source = Query1_a,
// 存在しない列 colX と MissingField.UseNull を指定
#"Removed Other Columns" = Table.SelectColumns(Source,{"col1", "col2", "colX"}, MissingField.UseNull)
in
#"Removed Other Columns"
| col1 | col2 | colX |
|---|---|---|
| a | 10 | null |
| b | 15 | null |
| c | 20 | null |
| d | 25 | null |
情報
Query and Data Modeling Languages | MSDN