ソースデータにあるはずと思っていた列やフィールドが存在しないことがあるなんてわりとよくあることで、これについて Power Query ではどうしておくかなってこと。
Expression.Error: テーブルの列 '日付' が見つかりませんでした。
なんていうのはPower Query エディターでよく見かけます。
いきなり別件ですが、CSV や Excel ワークシート上のデータを読み込むとき "型の検出" が行われるのが既定なのでムキーってなる人はオフにするとかできますのでお好みでどうぞ。Power BI Desktop でも同じ。
で、列のデータ型変換(Table.TransformColumnTypes)はこのあといくつかの手段で解決できるものではないので早々にあきらめて。
挙動を指定できる関数
MissingField.Type
MissingField.Type
想定より少ない列を含む行内で不足している値に対して予期されるアクションを指定します。
- MissingField.Error
- MissingField.Ignore
- MissingField.UseNull
MissingField.Error : Expression.Error になりクエリの評価ができなくなる(既定値)
MissingField.Ignore : 無視してクエリの評価は続行
MissingField.UseNull : null で補完してクエリの評価を続行
MissingField.Type を指定できる関数
MissingField.Type の定数すべてを受け付けるとは限らないけれども、MissingField.Type のオプション引数が定義されている関数。サポートされていない環境があったとしてそれはバージョンによるもの。
Record.RemoveFields
指定されたフィールドを入力レコードから削除します。
Record.RenameFields
{ old, new } という形式のリストを使用して、名前の変更を適用します。
Record.ReorderFields
指定されたフィールドを新しい順序で並べ替えます。
Record.SelectFields
指定されたフィールドのみを含むレコードを返します。
Record.TransformFields
指定された変換を適用したレコードを返します。
Table.FromRecords
レコードのリストをテーブルに変換します。
Table.SelectColumns
指定された列のみを含むテーブルを返します。
Table.RenameColumns
{old, new} という形式の名前の変更を適用します。
Table.ReorderColumns
指定された順序で列を含むテーブルを返します。
Table.RemoveColumns
指定された列を削除します。
Table.TransformColumns
{ column, transform } という形式の変換を適用します。
いくつかサンプル
let
Source = #table(
{"Column1","Column2","Column3"},
{}
),
SelectColumns = Table.SelectColumns(
Source,
{"Column1","ColumnX"},
MissingField.Ignore
),
ColumnNames = Table.ColumnNames(SelectColumns)
in
ColumnNames // {"Column1"}
let
Source = #table(
{"Column1","Column2","Column3"},
{{1, 1, 1}}
),
SelectColumns = Table.SelectColumns(
Source,
{"Column2", "Column1","ColumnX"},
MissingField.UseNull
)
in
SelectColumns
列の順番も変えてみた。
フィールドアクセスで挙動を指定
例えば Table.AddColumn で 存在しない列を参照したとき。
let
Source = #table(
{"Column1", "Column2"},
{
{1, 1}
}
),
AddedCustom1 = Table.AddColumn(
Source,
"Custom1",
each [Column2]
),
RemovedColumn2 = Table.RemoveColumns(
AddedCustom1,
{"Column2"}
),
AddedCustom2 = Table.AddColumn(
RemovedColumn2,
"Custom2",
each [Column2]
)
in
AddedCustom2
クエリの評価はされるもののエラーとなる値が存在する。まぁそうですよね。
Expression.Error:レコードのフィールド 'Column2' が見つかりませんでした。
let
Source = #table(
{"Column1", "Column2"},
{
{1, 1}
}
),
AddedCustom1 = Table.AddColumn(
Source,
"Custom1",
each [Column2]? // ココ
),
RemovedColumn2 = Table.RemoveColumns(
AddedCustom1,
{"Column2"}
),
AddedCustom2 = Table.AddColumn(
RemovedColumn2,
"Custom2",
each [Column2]? // ココ
)
in
AddedCustom2
ダイアログでの投入はこんな感じ
テーブルの行は record として参照できて、フィールドの値は "[ ]" ブラケットで指定するのだけど、"?"を追記すると、フィールドが存在しない場合 null を返し、存在する場合その値を返す。
フィールドの値が record のとき、そのフィールドの値は [ColumnName][FieldName] と繋げばアクセス可能で、[ColumnName]?[FieldName]? とすればよい。
列 / フィールド ではないけれども、"{ }" アイテムアクセスでも使用可能
# table(null, {}){0}? = null