Power BIに限らず､住所を処理することは意外とめんどくさい｡
日本の地名については､どうしてだかわからないが住所の最上位の区分さえ｢都道府県｣なんていうふうに分かれており､統一感がない｡
本稿は､とある住所を含んだデータをPower BIで処理したときのお話｡
住所はある､座標データもある､だが…
ご存じの方も多いとは思いますが､Power BIでは住所や座標データを用いて地図上に地点を表示する機能が備わっています｡
精度を求める場合には座標データが必要となりますが､そのようなデータを準備するのはちょっとめんどくさいです｡ジオコードAPI等を使用して振るのが一般的になってしまうかと思いますが､幸運にも最初から経度緯度データを含んだデータを使用する機会がありました｡
おぉ、結構良いデータじゃん！
地図上でポイントを示して検索できると便利そうだぞ！
地図にマッピングすると､簡単に地点が表示され､なかなか良いなと思っていましたが､そのうちに
- 地域によってフィルタを掛けたい
データは15万件ほどあるが､Power BIで表現できるのは3200点まで｡
表示している範囲で自動調整されることもないので､地図上に表示されるデータを3200件未満にフィルタする必要があった - 居住地域によって一覧を絞り込んだりするドリルダウンがやりたくなるだろう
ということで､住所データを｢都道府県｣｢市区町村｣で分解できればベターです｡
住所1と住所2はあるが…
そのデータは優秀なことに
- 住所１(都道府県+市区)
- 住所2(住所1以下)
この｢住所１｣で県名と市区町村を分解すれば余裕っしょ
と軽く考えていました｡
しかし､実際にはそんなに甘くありませんでした｡
作戦1 ｢都道府県｣を用いて分割
最初に思いついたのは､｢都｣｢道｣｢府｣｢県｣の4文字を起点に分割するという作戦です｡
ネットの記事を参考にしてサクッとこんなDAX式を挿入しました｡
= Table.SplitColumn(前のステップ名, "住所１", Splitter.SplitTextByCharacterTransition({"都","道","府","県"}, (c) => not List.Contains({"都","道","府","県"}, c)), {"住所１.1", "住所１.2"})ぱっと見､ちゃんと都道府県の分解ができているようです｡
これはすごい！
簡単簡単！
これで完成かな?と思っていたら…
よくよく完成したリストをみるとちょっとおかしい｡
どうやら､市区町村のレベルで｢都道府県｣の文字が含まれている地名があると､そちらを誤認してしまうようでした｡
例えば､｢都筑区｣とか｢府中｣とかですね｡
しばらく改良方法を検討しましたが良いアイデアが浮かばず､このままの利用は断念｡
ですが､どうやらDAXでどうにかできそうな手応えは掴めました｡
作戦2 ゴーインかつゴーイングマイウェイな置換
都道府県があるとダメということは､例えば｢県｣で統一されていればどうでしょうか?
ということで､以下のような強引な作戦を考えました｡
- 北海道､東京都､京都府､大阪府を全部北海県､東京県､京都県､大阪県に置換
- 最初に出現する｢県｣で分解する
- 1.と逆変換で復元
この方法であれば､最初に登場するものだけを識別すれば良さそうなので､うまく行きそうです｡
｢県｣に区切り文字を統一
｢北海道｣､｢東京都｣､｢京都府｣､｢大阪府｣を選択して｢価の置換｣で置き換えします｡
結果､こんなコードが生成されます｡(｢北海道｣の例｡他の変換も中身違いで同じ方針となります｡)
= Table.ReplaceValue(前のステップ名,"北海道","北海県",Replacer.ReplaceText,{"住所１"}),なお､｢東京都｣と｢京都府｣の処理順は注意が必要です｡
なんと､東京都には府中市があり､｢東京都府中市｣という｢京都府｣を含む地名が存在しますw
そのため､｢東京都｣を｢京都府｣の前に処理しましょう｡
日本の地名､ややこしすぎ！
｢県｣で分割
区切り文字｢県｣を指定して分割します｡
結果､こんなコードが生成されます｡
= Table.SplitColumn(前のステップ名, "住所１", Splitter.SplitTextByCharacterTransition({"県"}, (c) => not List.Contains({"県"}, c)), {"住所１.1", "住所１.2"}),最初の変換を元に戻す
要領は最初と同じです。
分解できた都道府県の項目を先の手順の置換元と先を逆にしてやればよいでしょう。
= Table.ReplaceValue(前のステップ名,"北海県","北海道",Replacer.ReplaceText,{"住所１.1"}),さらに､｢あの｣並び順にならない問題が…
都道府県での分解がうまく出来たところで､地域抽出条件に県と市区町村の階層構造が出来上がりますので､早速スライサーを作成して見てみました｡
そしてものすごい違和感が…
分解した子供の並び順はともかくとして､都道府県の並び順も文字コード順になっている！
| 県名 |
| 愛知県 |
| 愛媛県 |
| 茨城県 |
| 岡山県 |
| ︙ |
しかし､都道府県の並び順でよく見かけるのは北海道を一番上とした｢あの｣並び順です｡
| 県名 |
| 北海道 |
| 青森県 |
| 岩手県 |
| 宮城県 |
| ︙ |
｢あの｣順番ってなんなの?というと､物流業界でシステムを見てきたのですぐにわかります｡
あの並び順は｢JIS地区コード｣の｢県コード｣順です｡
ここまでわかればあとは実装あるのみです。
- 県コードと県名のテーブルを作成する
- 先に分解した県名と、新たに含めた県コード、県名のテーブルと県名でリレーションシップを設定する
- 県コードでソートする
ができればバッチリです。
県コードと県名のテーブルを作成する
47個なので調べながらコツコツ作っても良さそうですが、私はCSVのデータをダウンロードして使用しました。
DAXでイチからデータを作るのも可能そうでしたが、かなり面倒そうだったのでソースデータを作って読み込ませたほうが早い考え、そのようにしています。
こんな感じのデータです
| 都道府県コード | 都道府県名 |
| 01 | 北海道 |
| 02 | 青森県 |
| 03 | 岩手県 |
| 04 | 宮城県 |
| ︙ | ︙ |
県名テーブルと県名のリレーションシップの作成
データとこのマスタのリレーションシップを設定してやればOKです。
こんな感じでデータ側(この例では｢Data｣テーブルの｢事業所住所(都道府県)｣)の県項目とマスタ側(この例では｢都道府県｣テーブルの｢都道府県名｣)の都道府県名を管理する項目同士を設定すればよいです｡
県コードでソートする
ちょっとひと工夫が必要です。
わかりにくい部分なので画面キャプチャつけた説明入れます。
県テーブルの県名のソートキーを指定する
Power BIでは今回扱った県名とコードのような関係のデータ間でソート順を予め指定させることができます。
データビューで県名を管理するテーブルを表示し､県名のフィールドを選択します｡
例では､｢都道府県｣テーブルの｢都道府県名｣選択してデータカテゴリを｢州または都道府県｣に設定します
さらに､列で並び替えを選び､コードを管理しているフィールドを選択します｡
例では､｢都道府県コード｣を選択しています｡
ビジュアルで使用する項目には、県テーブルの県名を使用する
データ上に存在する都道府県名にはソートキーの指定ありませんので、そのままビジュアルで使用しても文字コード順でソートされてしまいます。
ビジュアル化の際に、県名を使用するフィールドにはデータではなく県名テーブルの「県名」を使用して配置します。
これにより、ビジュアル上のソート順は「県名テーブル」の「県コード」が使用されることになります。
今後の課題
｢都道府県｣での分解は成功しましたが､｢市区町村｣になると新たな課題が出てきます｡
例えば｢市｣なんてやろうとすると､｢市川市｣｢市原市｣｢四日市市｣なんていう地名が障壁になりそうです｡
住所コードと部分一致でぶつけるなどの方法も検討しましたが､Power BIのETLツールである｢Power Query｣のあいまい検索はちょっと思ったものと違う結果になったりと､良さそうな機能はいま時点では見つけられませんでした｡
作成したビジュアルの紹介
上記のようにして作成したビジュアルをブログに埋め込んで公開しています。
本ブログとは別のサイトになりますが、なにかの参考になればと思います。
コメント