Power Query：住所から地域名を表示する - なんでもエクセル（Nandemo Excel）

例えば住所を地域別に分類したい時、アプローチの方法は色々ある。アイテムアクセスを使うか列のマージを使うか。一長一短あるので使いどころ次第かと思う。

住所を地域別に分類する

最初に「住所一覧」と「地域区分」のテーブルがあるものとして、まずはそれぞれのクエリを作成する。この時ある程度、加工しておくと使い勝手がいい。住所一覧には「都道府県名」列を追加しておこう。都道府県名の抽出は、「左から４文字目が『県』だったら４文字、それ以外は３文字を左から抜き出す」でＯＫ。

住所一覧

let
    ソース = Excel.CurrentWorkbook(){[Name="住所一覧"]}[Content],
    型の変更 = Table.TransformColumnTypes(ソース, {"住所", type text}),
    都道府県列の追加 = Table.AddColumn(
        型の変更, "都道府県名", each
        Text.Start([住所], 3+Number.From(Text.Middle([住所], 3, 1)="県")),
        type text
    )
in
    都道府県列の追加

地域区分表には順番列（最初から入っているなら飛ばしてＯＫ）と地域ごとの連番列を追加する。グループごとに連番を付加する方法は、前回の記事参照のこと。

地域区分

let
    ソース = Excel.CurrentWorkbook(){[Name="地域区分"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース, {
        {"区分", type text}, {"都道府県名", type text}
    }),
    都道府県分割 = Table.TransformColumns(
        変更された型, {"都道府県名", each Text.Split(_, "、"), type list}
    ),
    順番列の追加 = Table.AddIndexColumn(
        都道府県分割, "順番", 1, 1, Int64.Type
    ),
    列の展開1 = Table.ExpandListColumn(順番列の追加, "都道府県名"),
    行のグループ化 = Table.Group(
        列の展開1, {"区分"}, {
            "テーブル",
            each Table.AddIndexColumn(_, "連番", 1, 1),
            type table [都道府県名=text, 順番=number, 連番=number]
        }
    ),
    列の展開2 = Table.ExpandTableColumn(
        行のグループ化, "テーブル", {"都道府県名", "順番", "連番"}
    )
in
    列の展開2

こうしておくことと、後でソートしやすくなる。

ではまず、元の住所の順番通りに地域区分列を追加する場合。列のマージだと展開時に順番がずれてしまうので、アイテムアクセスのほうが便利はいい。


順番そのままで区分列を追加する方法
let
    区分列の追加 = Table.AddColumn(
        住所一覧, "区分", each
            let 都道府県名=[都道府県名]
            in 地域区分{[都道府県名=[都道府県名]]}[区分],
        type text
    ),
    列の削除 = Table.RemoveColumns(区分列の追加,{"都道府県名"})
in
    列の削除

次は順番を地域区分の順にする場合。こっちはマージしてからソートするのがお勧め。


地域区分の順に区分列を追加する方法１
let
    クエリのマージ = Table.NestedJoin(
        住所一覧, {"都道府県名"},
        地域区分, {"都道府県名"},
        "マージ", JoinKind.LeftOuter
    ),
    マージ列の展開 = Table.ExpandTableColumn(
        クエリのマージ, "マージ",
        {"順番", "連番", "区分"}
    ),
    行のソート = Table.Sort(マージ列の展開, {{"順番", 0}, {"連番", 0}}),
    列の選択 = Table.SelectColumns(行のソート, {"区分", "住所"})
in
    列の選択

「地域区分のほうにマージしてやれば」と思うかもしれないけど、それをやると同じ都道府県の住所の中で順番がずれてしまう。


地域区分の順に区分列を追加する方法１
let
    クエリのマージ = Table.NestedJoin(
        地域区分, {"都道府県名"},
        住所一覧, {"都道府県名"},
        "マージ", JoinKind.LeftOuter
    ),
    マージ列の展開 = Table.ExpandTableColumn(
        クエリのマージ, "マージ", {"住所"}
    ),
    行の抽出 = Table.SelectRows(マージ列の展開, each [住所] <> null),
    行のソート = Table.Sort(行の抽出, {{"順番", 0}, {"連番", 0}}),
    列の選択 = Table.SelectColumns(行のソート,{"区分", "住所"})
in
    列の選択