全角の数字やアルファベット、記号がデータに含まれていることがあります。集計するためには半角に揃えたいし、なんとかしたい。公的機関から提供されるデータなどで顕著にみられることです。でも、Power BI Desktop や Excel などで多く使用する クエリ エディター、つまり Power Query には 全角文字を半角文字に変換する関数は用意されていません。ないなら仕方がないのでなんとかしましょう、文字の置換で。データソース側で対応できないそんなときに。
いくつかの方法がある - 置換
超シンプルなサンプルでいろいろ試してみます。
// 全角数値 を 半角数値に
// クエリ エディタで操作しやすいよう テーブルにした
let
Source = #table(
{"日付時刻"},
{
{"平成２９年８月３０日 １７時５６分２秒"}
,{"平成２９年９月３０日 １６時１５分００秒"}
,{"２０１７年１０月３０日 ６時０３分００秒"}
,{"平成29年10月30日 6時03分00秒"}
}
)
in
Source
試しに日付/時刻(datetime)にパース
let
ソース = サンプル,
変更された型 = Table.TransformColumnTypes(ソース,{{"日付時刻", type datetime}}, "ja-JP")
in
変更された型
| 日付時刻 |
|---|
| Error |
| Error |
| Error |
| 2017/10/30 6:03:00 |
DataFormat.Error: 指定された入力を DateTime 値に解析できませんでした。
全角数字が原因になっていることがわかる。
テーブルでの置換 - Table.ReplaceValue
Table.ReplaceValue で テーブルの列(column)の値に置換ができるので、このサンプルでは ０ ~ ９ を 0 ~ 9 に置換するよう繰り返せばよい。
Table.ReplaceValue | Power Query M function reference
Table.ReplaceValue(
table as table,
oldValue as any,
newValue as any,
replacer as function,
columnsToSearch as {Text}
) as table
columnsToSearch : 列名表す文字列のリスト
let
ソース = サンプル,
置き換えられた値 = Table.ReplaceValue(ソース,"０","0",Replacer.ReplaceText,{"日付時刻"}),
置き換えられた値1 = Table.ReplaceValue(置き換えられた値,"１","1",Replacer.ReplaceText,{"日付時刻"}),
置き換えられた値3 = Table.ReplaceValue(置き換えられた値2,"３","3",Replacer.ReplaceText,{"日付時刻"}),
置き換えられた値4 = Table.ReplaceValue(置き換えられた値3,"４","4",Replacer.ReplaceText,{"日付時刻"}),
置き換えられた値5 = Table.ReplaceValue(置き換えられた値4,"５","5",Replacer.ReplaceText,{"日付時刻"}),
置き換えられた値6 = Table.ReplaceValue(置き換えられた値5,"６","6",Replacer.ReplaceText,{"日付時刻"}),
置き換えられた値7 = Table.ReplaceValue(置き換えられた値6,"７","7",Replacer.ReplaceText,{"日付時刻"}),
置き換えられた値8 = Table.ReplaceValue(置き換えられた値7,"８","8",Replacer.ReplaceText,{"日付時刻"}),
置き換えられた値9 = Table.ReplaceValue(置き換えられた値8,"９","9",Replacer.ReplaceText,{"日付時刻"}),
変更された型 = Table.TransformColumnTypes(置き換えられた値9,{{"日付時刻", type datetime}}, "ja-JP")
in
変更された型
ちからわざではあるけれでも、変換したい文字分置換を繰り返す。
リボンにあるコマンド値の置換を使うとTable.ReplaceValue になり、引数:columnsToSearch は置換の対象となる列名のリストなので複数の列に対し同様な置換を適用できることが特徴。
引数:replacer は、
- フィールド全体を置換するとき、Replacer.Value
- フィールドの一部を置換するとき、Replacer.Text
になる。
リストでの置換 - List.ReplaceMatchingItems
別の方法としてリストで文字列を扱う。
Text.ToList | Power Query M function reference
Text.ToList(text as text) as list
で、文字列を1文字づつのリストに変換し、
List.ReplaceMatchingItems | Power Query M function reference
List.ReplaceMatchingItems(
list as list,
replacements as any,
optional equationCriteria as any
) as list
replacements : 置換する組合せをリストで表し、そのリスト
{{old1, new1}, ... ,{oldn, newn}}
equationCriteria : いまひとつ不明。比較関数あたりが使われるか。
アイテムごとに置換をして
Text.Combine | Power Query M function reference
Text.Combine(list as list) as text
で、結合の方針。どのような加工がされるかテーブルで見れるように列の追加で記述。
let
ソース = サンプル,
列の追加1 = Table.AddColumn(
ソース,
"リスト化",
each Text.ToList([日付時刻])
),
列の追加2 = Table.AddColumn(
列の追加1,
"置換",
each
List.ReplaceMatchingItems(
[リスト化],
{
{"０", "0"}
,{"１", "1"}
,{"２", "2"}
,{"３", "3"}
,{"４", "4"}
,{"５", "5"}
,{"６", "6"}
,{"７", "7"}
,{"８", "8"}
,{"９", "9"}
}
)
),
列の追加3 = Table.AddColumn(
列の追加2,
"結合",
each Text.Combine([置換])
),
列の追加4 = Table.AddColumn(
列の追加3,
"解析",
each DateTime.From([結合], "ja-jp")
,type datetime
)
in
列の追加4
| 日付時刻 | リスト化 | 置換 | 結合 | 解析 |
|---|---|---|---|---|
| 平成２９年８月３０日 １７時５６分２秒 | List | List | 平成29年8月30日 17時56分2秒 | 2017/08/30 17:56:02 |
| 平成２９年９月３０日 １６時１５分００秒 | List | List | 平成29年9月30日 16時15分00秒 | 2017/09/30 16:15:00 |
| ２０１７年１０月３０日 ６時０３分００秒 | List | List | 2017年10月30日 6時03分00秒 | 2017/10/30 6:03:00 |
| 平成29年10月30日 6時03分00秒 | List | List | 平成29年10月30日 6時03分00秒 | 2017/10/30 6:03:00 |
もうすこし工夫する
List.ReplaceMatchingItems の 引数 : replacements について
連続するアイテムのリスト
昇順で連続するアイテムのリストは ".." で表現することができる。
{0 .. 3} = {0, 1, 2, 3,} // true
{"0" .. "3"} = {"0", "1", "2", "3"} //true //UTF-16
List.Count({3 .. 0}) = 0 // アイテムがないリスト
リストの結合 - List.Zip
アイテムのポジションを一致するように 2つのリストを結合する。
List.Zip | Power Query M function reference
List.Zip({{1, 2}, {3, 4}})
| List |
|---|
| {1, 3} |
| {2, 4} |
List.Zip({{1, 2}, {3}})
| List |
|---|
| {1, 3} |
| {2, null} |
工夫の結果
列の追加2 = Table.AddColumn(
列の追加1,
"置換",
each
List.ReplaceMatchingItems(
[リスト化],
List.Zip(
{
{"０" .. "９"}
,{"0" .. "9"}
}
)
)
)
英数字や記号も含めて関数に
全角スペースと英数字記号も半角にする関数にするにはこのような感じで
(originalText as text) as text =>
let
combination = List.Zip(
{
{Character.FromNumber(0x3000), "！".."～"},
{Character.FromNumber(0x20), "!".."~"}
}
),
textList = List.ReplaceMatchingItems(
Text.ToList(originalText),
combination
)
in
Text.Combine(textList)
ここ最近(2021-04)こんな感じのトラブルが発生するようです。
調子悪かったりしたらこっちで使うとよいかも。あえて丁寧な記述したら事象は収まるっていうね。リストコンストラクタ "{ }" で list 評価されるときにこれまでになかった挙動が発生しているのかなと。
(originalText as text) as text =>
let
combination = List.Zip(
{
List.Combine( { { Character.FromNumber(0x3000) }, { "！".."～" } } ),
List.Combine( { { Character.FromNumber(0x20) }, { "!".."~" } } )
}
),
textList = List.ReplaceMatchingItems(
Text.ToList(originalText),
combination
)
in
Text.Combine(textList)
投票してください
やっぱり変換する関数は欲しいよねと思うので、
ASC function to M for converting Zen Chars to Han Chars - Microsoft Power BI
に ぜひ Vote。