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

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

# いくつかの方法がある - 置換 
超シンプルなサンプルでいろいろ試してみます。

```text:サンプル
// 全角数値 を 半角数値に
// クエリ エディタで操作しやすいよう テーブルにした
let
    Source = #table(
        {"日付時刻"},
        {
            {"平成２９年８月３０日 １７時５６分２秒"}
           ,{"平成２９年９月３０日 １６時１５分００秒"}
           ,{"２０１７年１０月３０日 ６時０３分００秒"}
           ,{"平成29年10月30日 6時03分00秒"}
        }
    )
in
    Source
```
試しに日付/時刻(datetime)にパース

```text
let
    ソース = サンプル,
    変更された型 = Table.TransformColumnTypes(ソース,{{"日付時刻", type datetime}}, "ja-JP")
in
    変更された型
```

|日付時刻|
|:--|
|Error|
|Error|
|Error|
|2017/10/30 6:03:00|

> DataFormat.Error: 指定された入力を DateTime 値に解析できませんでした。

全角数字が原因になっていることがわかる。

## テーブルでの置換 - [Table.ReplaceValue](https://msdn.microsoft.com/en-us/library/mt260856.aspx)
Table.ReplaceValue で テーブルの列(column)の値に置換ができるので、このサンプルでは ０ ~ ９ を 0 ~ 9 に置換するよう繰り返せばよい。

[Table.ReplaceValue | Power Query M function reference](https://msdn.microsoft.com/en-us/library/mt260856.aspx)

```text:構文
Table.ReplaceValue(
    table as table,
    oldValue as any,
    newValue as any,
    replacer as function,
    columnsToSearch as {Text}
) as table

columnsToSearch : 列名表す文字列のリスト
```

```text
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](https://msdn.microsoft.com/en-us/library/mt253640.aspx)
別の方法としてリストで文字列を扱う。

[Text.ToList | Power Query M function reference](https://msdn.microsoft.com/en-us/library/mt260475.aspx)

```text:構文
Text.ToList(text as text) as list 
```
で、文字列を1文字づつのリストに変換し、

[List.ReplaceMatchingItems | Power Query M function reference](https://msdn.microsoft.com/en-us/library/mt253640.aspx)

```text:構文
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](https://msdn.microsoft.com/en-us/library/mt253358.aspx)

```text:構文
Text.Combine(list as list) as text
```
で、結合の方針。どのような加工がされるかテーブルで見れるように列の追加で記述。

```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 について

### 連続するアイテムのリスト
昇順で連続するアイテムのリストは ".." で表現することができる。

```text
{0 .. 3} = {0, 1, 2, 3,} // true
{"0" .. "3"} = {"0", "1", "2", "3"} //true //UTF-16
List.Count({3 .. 0}) = 0  // アイテムがないリスト
```

### リストの結合 - [List.Zip](https://msdn.microsoft.com/en-us/library/mt742855.aspx) 
アイテムのポジションを一致するように 2つのリストを結合する。

[List.Zip | Power Query M function reference](https://msdn.microsoft.com/en-us/library/mt742855.aspx)

```text
List.Zip({{1, 2}, {3, 4}})
```
|List|
|:--:|
|{1, 3}|
|{2, 4}|

```text
List.Zip({{1, 2}, {3}})
```

|List|
|:--:|
|{1, 3}|
|{2, null}|

### 工夫の結果

```text:列の追加2
    列の追加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)こんな感じのトラブルが発生するようです。**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/03c00380-75ac-6630-62f5-664b89b8795f.png)
調子悪かったりしたらこっちで使うとよいかも。あえて丁寧な記述したら事象は収まるっていうね。リストコンストラクタ "{ }" で *list* 評価されるときにこれまでになかった挙動が発生しているのかなと。

```text
(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](https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13497783-asc-function-to-m-for-converting-zen-chars-to-han) 
に ぜひ Vote。

# 情報
- [Power Query M Reference](https://msdn.microsoft.com/en-us/library/mt211003.aspx)
  
# その他

- [Power BI 勉強会 - connpass](https://powerbi.connpass.com/)
- [Power BI Desktop を使い始めるには](http://qiita.com/PowerBIxyz/items/6c4735cb5b5712e70981)
- [Excel で Power Query を使い始めるには](http://qiita.com/PowerBIxyz/items/52e563037b855a099ca9)