誰だよこんなマスタ作ったのは？！

Power BIなどでみえる化を進めていると、ときどきどういうマスターテーブルに遭遇することがあります。

全角英数交じり。レガシーのシステムから出力しているから仕方がないとか事情があったりするので、一概に責められません。けれど精神衛生上よろしくないので、こういうのを一発で変換できる方法を探りました。

この記事では

Power BIやExcelで利用できるPower Queryを使います。

Power Queryとは？　と初めましての方はググってみるとこちらの記事が最初に出てきました。Power BIを使っている方は普段使っているはずですし、Excelでも利用できる協力なツールです。この機会に学んでおいて損なしですよ。

最終的なイメージ

イケてない列を、イケてる列に変換します。

テーブルをとりこむ

先ほどテスト用に作成したOneDrve上のイケてないExcelファイルのファイルパスをファイルの詳細から取得します。（ファイルのリンク取得からとってはいけませんよ）

取り込みは「Web」を使います。

なんでWeb取り込みを使うのか。このあたりは以前に書いたこちらの記事を参考にしてもらえるありがたいです。

https://qiita.com/DaddyDaddy/items/4b616f6d30fae5934c07

貼り付けたURLの最後が.xlsxになっているところがポイント！

OneDriveに保存してあるので、匿名では取得できません（取得できたら管理者に伝えたほうがよいかも）
組織アカウントを使ってサインインします。

uncoolテーブルを選択して「データの変換」をクリックします。

イケ文字変換

イケてないマスタが読み込めたら、「列の追加」＞「カスタム列」をクリックします。

追加する列名を適当に入力し、「カスタム列の式」に下記のコードをペタリと貼ります。2行目のかっこの中は変換元の列名に差し替えてください。

イケてるカスタム列追加

let
    t = [イケてないマスタ],
    result =
        if t = null then
            null
        else
            Text.Combine(
                List.Transform(
                    Text.ToList(t),
                    (c) =>
                        let
                            n = Character.ToNumber(c)
                        in
                            if n = null then
                                c
                            else if n = 12288 then
                                " "                         // 全角スペース → 半角スペース
                            else if n >= 65281 and n <= 65374 then
                                Character.FromNumber(n - 65248)  // 全角ASCII → 半角ASCII
                            else
                                c
                )
            )
in
    result

するとあら不思議！　全角でイライラつのる名称が半角の素敵な名称に早変わりしました。

何が起きているのか？

全角の記号とアルファベットはUnicodeのU+FF01～U+FF5Eの中に含まれていることが下記の図でわかります。

全角のアルファベットがこのように並んでいるということは、同じように半角のアルファベットも存在します。

Unicode のコードポイントは通常 U+ に続けて16進数で表記されます。

Unicodeでは、全角英数字・記号は、U+FF01 ～ U+FF5E という連続した範囲に配置されています。
一方、対応する半角（ASCII）は、U+0021 ～ U+007E に配置されています。
つまりこうなっています：

半角:  U+0021 ～ U+007E
全角:  U+FF01 ～ U+FF5E

文字コードはCharacter.ToNumber() 関数に渡すことで10進数に変換できます。

U+FF01 = 0xFF01 = 65281
U+0021 = 0x0021 =    33

同じ並びをしている２つの表の開始位置の差は 65281 - 33 = 65248
なので、前述のカスタム列追加にコピーしたコードは、全角ASCIIの範囲の文字がやってきたら、65248ずらした位置にある文字に置き換えるということをしています。

ついでに全角スペースは半角に。どちらにも当てはまらない場合にはそのまま返すことで素敵な変換を実現しています。

変換の中身は単純なif文なので、ASCII範囲に入らない文字で特殊対応したい場合には、条件を加えるとよいでしょう。

カスタム関数版

列追加がうまくいったので、ついでにカスタム関数版の変換方法も試してみましょう。
カスタム関数は複数のクエリの中で同じ変換をおこなうような場合に有効です。複雑な式を外に出して再利用するようなイメージです。

クエリの空きスペースで右クリック＞「新しいクエリ」＞「空のクエリ」　をクリックします。

追加されたクエリの上で右クリック＞「詳細エディタ」　をクリックします。

下記のコードをペタリと貼り付けて「完了」をクリックします。

イケてる変換関数

(text as nullable text) as nullable text =>
let
    // 全角ASCIIの基本範囲：U+FF01 ～ U+FF5E（！ ～ ～）
    // 半角ASCIIの基本範囲：U+0021 ～ U+007E
    // 差分は 65248
    ConvertChar = (c as text) as text =>
        let
            n = Character.ToNumber(c)
        in
            if n = null then
                c
            else if n = 12288 then
                " "                 // 全角スペース（U+3000）→ 半角スペース
            else if n >= 65281 and n <= 65374 then
                Character.FromNumber(n - 65248)  // 全角ASCII → 半角ASCII
            else
                c,

    result =
        if text = null then
            null
        else
            Text.Combine(List.Transform(Text.ToList(text), each ConvertChar(_)))
in
    result

こんな感じになったら成功です。

uncoolのテーブルを選択し、「列の追加」＞「カスタム関数の呼び出し」をクリックします。

追加する列名を適当に入力し、先ほど作ったカスタム関数（今回はクエリ１）を選択し、最後に変換元の値が入っている列を選択して「OK」をクリックします。

関数によるイケてる変換された列が追加されました！

再利用しやすいようにカスタム関数には名前を付けておくことをお勧めします。後でつけてもちゃんと連携しているほかのテーブル内のクエリにも反映してくれるのがうれしいです。

最後に

文字コードは奥が深すぎますが、全角、半角の日本語文字範囲であればマジックナンバーを引き算することで変換ができるので、遭遇したらこの記事を思い出してコピペして使ってもらえると嬉しいです。

自分用のメモも兼ねていたりします。

こんな人が書いています

こちらの記事はランゲルハンス島のDDさんが紹介しました。ブログでクラウドフローのTIPSのようなものを書いたり、Qiita記事を書いたりしていますのでご贔屓に。

フォローやいいねいただけると嬉しいです。
関西のPowerPlatform系の勉強会にときどき出没しますので、「あのアイコンの顔の人だ！」と、気軽に声をかけていただけると喜びます！