[Power BI] Table.AddRankColumnで月別ランク表を作る

（この記事は2022年8月20日の「PBIJP Power Query 秘密特訓「虎の穴」炎の復活編 #17」で使用したものを若干アップグレードしています）

　Power BIで月別の成績をマトリックスで表示します。
　今回は、「理科年表（平成14年版），（国立天文台編））」を元に国土交通省が作成した県庁所在地の平年降水量の表を使用します。

　この表から、月別に降水量の多い場所トップ10をマトリックスに表示させたいと思います。

Webページのテーブル取り込み

　Webページからデータをインポートします。


　Webページを解析してデータを取得してくれる機能は素晴らしく使いやすいですが、これで出来上がるコードは以下のようになってしまいます。

　このままでも問題ないのですが、もっとスマートにCSSセレクタを書きたいのと、 Web.BrowserContents を Web.Contents に書き換えたいと思います。Power BI Serviceで使用するとき、 Web.BrowserContents だと On-premises data gateway が必要になるためです。ところが、元のWebページはSJISで書かれているため、このままだと文字化けしてしまいます。そこで、 Text.FromBinary を使ってSJISをエンコードして読み込みます。

テーブル取得

    Source = 
        Text.FromBinary(
            Web.Contents("https://www.mlit.go.jp/river/toukei_chousa/kasen/jiten/toukei/birn38p.html"),
            932  // sjisのページを読み込む
        ),
    ExtractedTableFromHtml = 
        Html.Table(
            Source, 
            {
                {"Column1", "TH"},
                {"Column2", "TH[colspan], TH:not([colspan]):nth-child(2)"}, 
                {"Column3", "TH[colspan] + TH:nth-child(2), TD:nth-child(3)"},
                {"Column4", "TH[colspan] ~ TH:nth-child(3), TD:nth-child(4)"},
                {"Column5", "TH[colspan] ~ TH:nth-child(4), TD:nth-child(5)"},
                {"Column6", "TH[colspan] ~ TH:nth-child(5), TD:nth-child(6)"},
                {"Column7", "TH[colspan] ~ TH:nth-child(6), TD:nth-child(7)"},
                {"Column8", "TH[colspan] ~ TH:nth-child(7), TD:nth-child(8)"},
                {"Column9", "TH[colspan] ~ TH:nth-child(8), TD:nth-child(9)"},
                {"Column10", "TH[colspan] ~ TH:nth-child(9), TD:nth-child(10)"},
                {"Column11", "TH[colspan] ~ TH:nth-child(10), TD:nth-child(11)"},
                {"Column12", "TH[colspan] ~ TH:nth-child(11), TD:nth-child(12)"},
                {"Column13", "TH[colspan] ~ TH:nth-child(12), TD:nth-child(13)"},
                {"Column14", "TH[colspan] ~ TH:nth-child(13), TD:nth-child(14)"},
                {"Column15", "TH[colspan] ~ TH:nth-child(14), TD:nth-child(15)"}
            },
            [RowSelector="TABLE:nth-child(7) > * > TR"]
        ),

テーブルの整理

　最後の行にある合計行を削除 (Table.RemoveLastN) し、１行目をヘッダーに昇格 (Table.PromoteHeaders) させます。
　その後、地点名の文字の間にある空白を除去します。

Table.ReplaceValue構文

Table.ReplaceValue(
    table as table, 
    oldValue as any, 
    newValue as any, 
    replacer as function,   // Replacer.ReplaceText or Replacer.ReplaceValue
    columnsToSearch as list
) as table

地点名の空白を除去

    ReplacedValue = 
        Table.ReplaceValue(
            PromotedHeaders,
            "　",
            "",
            Replacer.ReplaceText,
            {"都道府県（地点）_1"}
        ),

月ごとの列をUNPIVOTする

　月ごとの数値が横に並んでいるので、これを月ごとのデータに変えます。

Table.UnpivotOtherColumns構文

Table.UnpivotOtherColumns(
    table as table, 
    pivotColumns as list, 
    attributeColumn as text, 
    valueColumn as text
) as table

月ごとのデータに分ける

UnpivotedColumns = 
    Table.UnpivotOtherColumns(
        RemovedColumns, 
        {"地点"}, 
        "属性", 
        "値"
),

Table.Groupでデータを月ごとにまとめてテーブルを作る

　順位付けをするために、 Table.Group を使って月ごとのテーブルを作成します。

Table.Group構文

Table.Group(
    table as table, 
    key as any, 
    aggregatedColumns as list, 
    optional groupKind as nullable number,  // GroupKind.Local or GroupKind.Global
    optional comparer as nullable function  // 比較関数 Comparer.Equals など
) as table

Table.Groupで月ごとにまとめる

GroupedRows = 
    Table.Group(
        RenamedColumns2, 
        {"月"}, 
        {
            {
                "月別", 
                each _, 
                type table [地点=text, 月=nullable number, 降水量=nullable number]
            }
        }
    ),

Table.AddRankColumnでランクを付ける

　先の Table.Group の式の中に、ランク項目を追加する Table.AddRankColum 処理を加えます。

Table.AddRankColumn構文

Table.AddRankColumn(
    table as table, 
    newColumnName as text, 
    comparisonCriteria as any, 
    optional options as nullable record
) as table

Group処理の中で月別ランク列を追加

GroupedRows = 
    Table.Group(
        RenamedColumns2, 
        {"月"}, 
        {
            {
                "月別", 
                each // ランク付け項目の追加
                    Table.AddRankColumn(
                        _,
                        "ランク",
                        {"降水量", Order.Descending},
                        [RankKind = RankKind.Ordinal]
                    ),
                type table [地点=text, 月=text, 降水量=number, ランク= Int64.Type]
            }
        }
    ),

　RankKind では、値の等しいアイテムが存在するときの処理を選択でき、利用目的に応じて指定を行います。

名前	値	説明
RankKind.Competition	0	等しいアイテムには同一番号が振られ、次の番号が欠番になります。 (ex. 1, 1, 3, 4)
RankKind.Dense	1	等しいアイテムには同一番号が振られ、その次には続きの番号が振られます。 (ex. 1, 1, 2, 3)
RankKind.Ordinal	2	等しいアイテムにも固有の番号が振られます。 (ex. 1, 2, 3, 4)

　今回は、ランク表で同じランクが発生しないよう RankKind.Ordinal を使用しています。

　「月」以外の項目を展開します。

ゼロ幅スペース (zero width space, ZWSP)

　このままマトリックスを作成すると、以下のように月の並びがおかしくなります。

　数字であればきちんと並びますが、最後の年間合計の降水量も加えたいので、文字のまま１から始まるようにするために、２文字の場合は頭に「ゼロ幅スペース」 を入れます。この文字は、 Character.FromNumber(8203) あるいは "#(200B)" で表すことができます。8203の16進数表記が200Bになります。

ReplacedValue2 = 
    Table.TransformColumns(
        ChangedType2,
        {
            {
                "月",
                each 
                    if Text.Contains(_, "月") 
                    then Text.End(Text.Format("#(200B)#{0}", {_}), 3)   // 1月から9月にはゼロ幅スペースを頭につける 
                    else "年間"  // 「合計」は「年間」に置き換える
            }
        }
    )

マトリックスを作成する

　以上で作成したデータをマトリックスに書き出します。

全コード

全コード

全コード

let
    Source = 
        Text.FromBinary(
            Web.Contents("https://www.mlit.go.jp/river/toukei_chousa/kasen/jiten/toukei/birn38p.html"),
            932  // sjis
        ),
    ExtractedTableFromHtml = 
        Html.Table(
            Source, 
            {
                {"Column1", "TH"},
                {"Column2", "TH[colspan], TH:not([colspan]):nth-child(2)"}, 
                {"Column3", "TH[colspan] + TH:nth-child(2), TD:nth-child(3)"},
                {"Column4", "TH[colspan] ~ TH:nth-child(3), TD:nth-child(4)"},
                {"Column5", "TH[colspan] ~ TH:nth-child(4), TD:nth-child(5)"},
                {"Column6", "TH[colspan] ~ TH:nth-child(5), TD:nth-child(6)"},
                {"Column7", "TH[colspan] ~ TH:nth-child(6), TD:nth-child(7)"},
                {"Column8", "TH[colspan] ~ TH:nth-child(7), TD:nth-child(8)"},
                {"Column9", "TH[colspan] ~ TH:nth-child(8), TD:nth-child(9)"},
                {"Column10", "TH[colspan] ~ TH:nth-child(9), TD:nth-child(10)"},
                {"Column11", "TH[colspan] ~ TH:nth-child(10), TD:nth-child(11)"},
                {"Column12", "TH[colspan] ~ TH:nth-child(11), TD:nth-child(12)"},
                {"Column13", "TH[colspan] ~ TH:nth-child(12), TD:nth-child(13)"},
                {"Column14", "TH[colspan] ~ TH:nth-child(13), TD:nth-child(14)"},
                {"Column15", "TH[colspan] ~ TH:nth-child(14), TD:nth-child(15)"}
            },
            [RowSelector="TABLE:nth-child(7) > * > TR"]
        ),
    RemovedBottomRows = 
        Table.RemoveLastN(
            ExtractedTableFromHtml,
            1
        ),
    PromotedHeaders = Table.PromoteHeaders(RemovedBottomRows, [PromoteAllScalars=true]),
    ReplacedValue = Table.ReplaceValue(PromotedHeaders,"　","",Replacer.ReplaceText,{"都道府県（地点）_1"}),
    RenamedColumns = Table.RenameColumns(ReplacedValue,{{"都道府県（地点）_1", "地点"}}),
    RemovedColumns = Table.RemoveColumns(RenamedColumns,{"都道府県（地点）"}),
    UnpivotedColumns = Table.UnpivotOtherColumns(RemovedColumns, {"地点"}, "属性", "値"),
    ChangedType = Table.TransformColumnTypes(UnpivotedColumns,{{"値", type number}}),
    RenamedColumns2 = Table.RenameColumns(ChangedType,{{"属性", "月"}, {"値", "降水量"}}),
    GroupedRows = 
        Table.Group(
            RenamedColumns2, 
            {"月"}, 
            {
                {
                    "月別", 
                    each 
                        Table.AddRankColumn(
                            _,
                            "ランク",
                            {"降水量", Order.Descending},
                            [RankKind = RankKind.Ordinal]
                        ),
                    type table [地点=text, 月=text, 降水量=number, ランク= Int64.Type]
                }
            }
        ),
    Expanded = Table.ExpandTableColumn(GroupedRows, "月別", {"地点", "降水量", "ランク"}, {"地点", "降水量", "ランク"}),
    ReplacedValue2 = 
        Table.TransformColumns(
            Expanded,
            {
                {
                    "月",
                    each 
                        if Text.Contains(_, "月") 
                        then Text.End(Text.Format("#(200B)#{0}", {_}), 3) 
                        else "年間",
                    type text
                }
            }
        )
in
    ReplacedValue2