第1章 データ変換テクニック
1.1 トリミング（Text.Trim）
トリミング（Trim）は、フィールド内のテキスト値の先頭と末尾から空白を削除するために使います。
let
Source =
Table.FromRows(
{
{1, "Bob", " 123-567 "},
{2, "Jim", "987-#(lf)6543"}
},
type table [CustomerID = text, Name = text, Phone = text]
),
#"Trimmed Text" =
Table.TransformColumns(
Source,
// transformOperations as list
{
{
"Phone",
Text.Trim,
type text
}
}
)
in
#"Trimmed Text"
Table.FromRecords({
[CustomerID = 1, Name = "Bob", Phone = "123-4567"],
[CustomerID = 2, Name = "Jim", Phone = "987-#(lf)6543"]
})
1.2 クリーン（Text.Clean）
クリーン（Clean）は、テキスト値からすべての制御文字を削除します。
let
Source =
Table.FromRows(
{
{1, "Bob", " 123-567 "},
{2, "Jim", "987-#(lf)6543"}
},
type table [CustomerID = text, Name = text, Phone = text]
),
#"Cleaned Text" =
Table.TransformColumns(
Source,
// transformOperations as list
{
{
"Phone",
Text.Clean,
type text
}
}
)
in
#"Trimmed Text"
Table.FromRecords({
[CustomerID = 1, Name = "Bob", Phone = " 123-567 "],
[CustomerID = 2, Name = "Jim", Phone = "987-6543"]
})
1.3 値の置換（Table.ReplaceValue）
シンプルな値の置換を行います。以下の例は、スペースをハイフンに変換しています。
let
Source =
Table.FromRows(
{
{1, "Bob", "123 567"},
{2, "Jim", "987 6543"}
},
type table [CustomerID = text, Name = text, Phone = text]
),
#"Replaced Value" =
Table.ReplaceValue(
Source,
" ", // oldValue
"-", // newValue
Replacer.ReplaceText, // replacer as function
{"Phone"} // columnsToSearch as list
)
in
#"Replaced Value"
1.4 プレフィックスの追加（Prefix）
「変換」タブの「書式」から「プレフィックスの追加」を使用して、ドメイン名の前に「https://」を追加します。
let
Source = Table.FromRows(
{
{"朝日新聞デジタル","www.asahi.com"},
{"Infoseek","www.infoseek.co.jp"},
{"ライブドア","news.livedoor.com"},
{"goo","news.goo.ne.jp"},
{"定番サイトJAPAN","teibansite.jp/net-news"}
},
type table [Name = text, URL = text]
),
#"Added Prefix" =
Table.TransformColumns(
Source,
{
{
"URL",
each "https://" & _, // データの前に"https://"をつける
type text
}
}
)
in
#"Added Prefix"
1.1や1.2では、Text.Trim やText.Clean が使われていましたが、これは関数です。each Text.Trim(_) とeach Text.Clean(_) が省略された書き方になっています。引数の _ は、変換される元のデータを表しています。
今回使用する、each "https://" & _ は、「それぞれの項目（each）に対して、元の価（_）の前にhttps:// をつける」という処理になっています。
1.5 サフィックスの追加（Suffix）
画像ファイル名に拡張子「.png」をつけます。
let
Source =
Table.FromRows(
{
{"Picuture01","Picuture01"},
{"Picuture02","Picuture02"},
{"Picuture03","Picuture03"}
},
type table [Name = text, File = text]
),
#"Added Suffix" =
Table.TransformColumns(
Source,
{
{
"File",
each _ & ".png", // データの後に".png"をつける
type text
}
}
)
in
#"Added Suffix"
1.6 区切り記号による列の分割（Table.SplitColumn）
Power Query には、列を分割する複数の方法があります。1つの方法は、区切り記号に基づいて列を分割することです。
let
Source =
Table.FromRows(
{
{1,"東京/日本/アジア"},
{2,"ワシントンD.C./アメリカ合衆国/アメリカ"},
{3,"ロンドン/イギリス/ヨーロッパ"}
},
type table [No = number, City = text]
),
#"Split Column by Delimiter" =
Table.SplitColumn(
Source,
"City",
Splitter.SplitTextByDelimiter(
"/",
QuoteStyle.Csv
),
{
"City",
"Country",
"Area"
}
)
in
#"Split Column by Delimiter"
区切り文字は削除され、区切り文字で区切られたテキストが列に分かれます。
1.7 区切り記号の前のテキスト（Text.BeforeDelimiter）
1.7.1 列の追加
メールアドレスの「＠」文字の前の部分を別の列に作成する場合、「列の追加」タブから「抽出」の中の「区切り記号の前のテキスト」を選択します。
let
Source =
Table.FromRows(
{
{"example1@example.com"},
{"test.user@example.org"},
{"dummy.email@example.net"}
},
type table [email = text]
),
#"Inserted Text Before Delimiter" =
Table.AddColumn(Source, "区切り記号の前のテキスト",
each Text.BeforeDelimiter([email], "@"),
type text
)
in
#"Inserted Text Before Delimiter"
1.7.2 変換
列を追加せず変換したい場合は、「変換」タブから「抽出」「区切り記号の前のテキスト」を選択します。
let
Source =
Table.FromRows(
{
{"example1@example.com"},
{"test.user@example.org"},
{"dummy.email@example.net"}
},
type table [email = text]
),
#"Extracted Text Before Delimiter" =
Table.TransformColumns(
Source, {
{
"email",
each Text.BeforeDelimiter(_, "@"),
type text
}
}
)
in
#"Extracted Text Before Delimiter"
Table.AddColumn の代わりにTable.TransformColumns が使われます。
1.8 区切り記号の後のテキスト（Text.AfterDelimiter）
1.8.1 列の追加
メールアドレスの最後の「.」に続く「com」や「org」などのトップレベルドメイン（Top-Level Domain, TLD）を抽出し、新しい列を作るには、「列の追加」タブから「変換」「区切り記号の後のテキスト」を選択します。
変換するテキストに「.」が複数含まれているため、最後の部分のみ取り出すには、「区切り記号の後のテキスト」ダイアログで「詳細設定オプション」を指定します。
let
Source =
Table.FromRows(
{
{"example1@example.com"},
{"test.user@example.org"},
{"dummy.email@example.net"}
},
type table [email = text]
),
#"Inserted Text After Delimiter" =
Table.AddColumn(
Source,
"区切り記号の後のテキスト",
each Text.AfterDelimiter(
[email],
".",
{
0,
RelativePosition.FromEnd
}
),
type text
)
in
#"Inserted Text After Delimiter"
1.8.2 変換
1.7と同様に、列の追加ではなく、元の列を変換したい場合は、「変換」タブから「変換」「区切り記号の後のテキスト」を選択します。
let
Source =
Table.FromRows(
{
{"example1@example.com"},
{"test.user@example.org"},
{"dummy.email@example.net"}
},
type table [email = text]
),
#"Extracted Text After Delimiter" =
Table.TransformColumns(
Source,
{
{
"email",
each
Text.AfterDelimiter(
_,
".",
{
0,
RelativePosition.FromEnd
}
)
,
type text
}
}
)
in
#"Extracted Text After Delimiter"
1.9 複数の区切り記号を指定する
複数の区切り記号を指定したい場合、GUIでは一度に処理できません。最初に１種類の区切り記号で分割した後、詳細エディタを開いて修正します。
今回は、最初に列を追加して「@」で分割します。
let
Source =
Table.FromRows(
{
{"example1@example.com"},
{"test.user@example.org"},
{"dummy.email@example.net"}
},
type table [email = text]
),
#"Added Custom" = Table.AddColumn(Source, "カスタム", each [email]),
#"Split Column by Delimiter" =
Table.SplitColumn(
#"Added Custom",
"カスタム",
Splitter.SplitTextByDelimiter(
"@",
QuoteStyle.Csv
),
{"カスタム.1", "カスタム.2"}
)
in
#"Split Column by Delimiter"
詳細エディタを開き、Splitter.SplitTextByDelimiter 関数をSplitter.SplitTextByAnyDelimiter 関数に書き換えます。
let
Source =
Table.FromRows(
{
{"example1@example.com"},
{"test.user@example.org"},
{"dummy.email@example.net"}
},
type table [email = text]
),
#"Added Custom" = Table.AddColumn(Source, "カスタム", each [email]),
#"Split Column by Delimiter" =
Table.SplitColumn(
#"Added Custom",
"カスタム",
// splitter as function
Splitter.SplitTextByAnyDelimiter(
{"@","."},
QuoteStyle.Csv
),
4 // optional columnNamesOrNumber as any
)
in
#"Split Column by Delimiter"
全てのデータで、同じ数で分割される場合は、Table.SplitColumn 関数の第３引数columnNamesOrNumber は省略可能です。しかし、今回のように、１番目のデータが３つに分割され、２番目、３番目は４つに分割される場合、列は３つしか作成されません。そこで、4項目作成するよう指定する必要があります。そして、1番目のデータの4列目にはnull になります。
columnNamesOrNumber は、列名のリストまたは負以外の数値を指定します。自動で作成されたクエリのように{"カスタム.1", "カスタム.2", "カスタム.3", "カスタム.4"} のように列名を指定することもできますが、単に4 と列数を指定することもできます。
1.10 データ型の変換
Power Queryでは、データ型を適切に変換することが、クエリ全体の効率性や正確性に影響を与える重要な要素です。GUIを利用した処理の後に自動的に作成されることがありますが、適切なタイミングで手動で行うようにするのがベストプラクティスです。
適切なタイミングとは、列に対する変換処理を行う前や、列の処理が完了した後です。
列の型変換は、Table.TransformColumnType関数を使用します。また、列の型を宣言するタイプ・クレイム（型要求）という仕組みについても理解しておくと安心です。
詳細は、『Power Query の「型」を探る』で5回に分けて説明しています。
Table.TransformColumnType : データ型の動的変換
let
// Excelファイルからデータをインポート
Source = Excel.Workbook(File.Contents("C:\Data\SalesData.xlsx"), null, true),
// テーブルの選択
SalesTable = Source{[Name="Sales"]}[Data],
// 動的にデータ型を変更する
ChangedType =
Table.TransformColumnTypes(
SalesTable,
{
{
"SalesDate",
each
if Value.Is(_, type datetime)
then type date
else type text
}
}
)
in
ChangedType
このコードでは、列のデータ型を動的に変換し、特定の条件（SalesDate列がdatetime型であればdate型に変換）に基づいてデータ型を決定しています。
このコードは、Table.TransformColumnTypes の動作について学ぶためのサンプルです。変換する列が特定されている場合には、このような作業は意味がありませんが、多くの列を持つ表で複数の列を変換したい場合に使うことがあるかもしれません。