Excel
PowerBI
PowerQuery

Power Query で 全角数字 から 半角数字 に変換するには

全角の数字やアルファベット、記号がデータに含まれていることがあります。集計するためには半角に揃えたいし、なんとかしたい。公的機関から提供されるデータなどで顕著にみられることです。でも、Power BI Desktop や Excel などで多く使用する クエリ エディター、つまり Power Query には 全角文字を半角文字に変換する関数は用意されていません。ないなら仕方がないのでなんとかしましょう、文字の置換で。データソース側で対応できないそんなときに。

いくつかの方法がある - 置換

超シンプルなサンプルでいろいろ試してみます。

サンプル
// 全角数値 を 半角数値に
// クエリ エディタで操作しやすいよう テーブルにした
let
    Source = #table(
        {"日付時刻"},
        {
            {"平成29年8月30日 17時56分2秒"}
           ,{"平成29年9月30日 16時15分00秒"}
           ,{"2017年10月30日 6時03分00秒"}
           ,{"平成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 を 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","0",Replacer.ReplaceText,{"日付時刻"}),
    置き換えられた値1 = Table.ReplaceValue(置き換えられた値,"1","1",Replacer.ReplaceText,{"日付時刻"}),
    置き換えられた値3 = Table.ReplaceValue(置き換えられた値2,"3","3",Replacer.ReplaceText,{"日付時刻"}),
    置き換えられた値4 = Table.ReplaceValue(置き換えられた値3,"4","4",Replacer.ReplaceText,{"日付時刻"}),
    置き換えられた値5 = Table.ReplaceValue(置き換えられた値4,"5","5",Replacer.ReplaceText,{"日付時刻"}),
    置き換えられた値6 = Table.ReplaceValue(置き換えられた値5,"6","6",Replacer.ReplaceText,{"日付時刻"}),
    置き換えられた値7 = Table.ReplaceValue(置き換えられた値6,"7","7",Replacer.ReplaceText,{"日付時刻"}),
    置き換えられた値8 = Table.ReplaceValue(置き換えられた値7,"8","8",Replacer.ReplaceText,{"日付時刻"}),
    置き換えられた値9 = Table.ReplaceValue(置き換えられた値8,"9","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", "0"}
                   ,{"1", "1"}
                   ,{"2", "2"}
                   ,{"3", "3"}
                   ,{"4", "4"}
                   ,{"5", "5"}
                   ,{"6", "6"}
                   ,{"7", "7"}
                   ,{"8", "8"}
                   ,{"9", "9"}
                }
            )
    ),
    列の追加3 = Table.AddColumn(
        列の追加2,
        "結合",
        each Text.Combine([置換])
    ),
    列の追加4 = Table.AddColumn(
        列の追加3,
        "解析",
        each DateTime.From([結合], "ja-jp")
       ,type datetime
    )
in
    列の追加4
日付時刻 リスト化 置換 結合 解析
平成29年8月30日 17時56分2秒 List List 平成29年8月30日 17時56分2秒 2017/08/30 17:56:02
平成29年9月30日 16時15分00秒 List List 平成29年9月30日 16時15分00秒 2017/09/30 16:15:00
2017年10月30日 6時03分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
    列の追加2 = Table.AddColumn(
        列の追加1,
        "置換",
        each
            List.ReplaceMatchingItems(
                [リスト化],
                List.Zip(
                    {
                        {"0" .. "9"}
                       ,{"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)

投票してください

やっぱり変換する関数は欲しいよねと思うので、
ASC function to M for converting Zen Chars to Han Chars - Microsoft Power BI
に ぜひ Vote。

情報

その他