今回のテーマはPowerQueryを使った住所に関する便利な小ネタです。
都道府県分割
例えば、以下のように都道府県と住所が一つにまとまったデータがあるとします。
これを都道府県とそれ以降の住所に分割します。
※ このデータは全国の県庁所在地から番地を抜いたものです。
まず住所列を右クリックし、列の分割‐数字から数字以外による分割を選択します。
次に数式バーの数式を以下のように変更します。
変更前
= Table.SplitColumn(変更された型, "住所", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"住所.1", "住所.2"})
変更後
= Table.SplitColumn(変更された型, "住所", Splitter.SplitTextByCharacterTransition({"都","道","府","県"}, (c) => not List.Contains({"都","道","府","県"}, c)), {"住所.1", "住所.2"})
これで無事、都道府県を区切り記号として残りの住所部分と切り離すことができました。
元々の { "0" .. "9" } は0から9までの数字のリストであり、Splitter.SplitTextByCharacterTransitionの(c) => not List.Containsのところで、0から9までの数字からそれらを含まない状態に変化した時に分割するという指示を与えていました。
そこの0から9までの数字を都道府県の文字に置き換えたわけです。
同様に市区町村も分割できます。
= Table.SplitColumn(文字の移行による列の分割, "住所.2", Splitter.SplitTextByCharacterTransition({"市","区","町","村"}, (c) => not List.Contains({"市","区","町","村"}, c)), {"住所.2.1", "住所.2.2"})
住所に限らず、何かの文字でテキストを分割したい場合は { } のリスト部分に文字の候補を並べることで分割できます。
都道府県の重複記述クレンジング
以下のように都道府県と住所が別の列であるのですが、住所には都道府県が記載されていたり、いなかったりする場合です。
この場合、都道府県が記載されている住所についてはすべて都道府県部分をクリアする手順です。
住所列を右クリックし、値の置換を選択します。
検索する値には適当に「あ」といれ、置換後は何も入れずブランクのままOKを押します。
次に数式バーの数式を以下のように変更します。
変更前
= Table.ReplaceValue(変更された型,"あ","",Replacer.ReplaceText,{"住所"})
変更後
= Table.ReplaceValue(変更された型,each[都道府県],"",Replacer.ReplaceText,{"住所"})
これで無事、住所側の都道府県がクリアされました。
eachの後の [ ] の中は削除したい値の含まれた列を意味しています。
[ ] （角括弧）は列の名前を指定するときに使用します。
（それに対して { } （波括弧）はリスト型、または0から始まるレコード行番号を指定するときに使います。）
もしこの後で重複のないきれいなフル住所を作りたければ、都道府県と住所を選んで変換（または列の追加）－列のマージを選んで結合すればOKです。
以上、手順として簡単ですが住所データのクレンジングに役立ちそうな小技でした。
