バスの料金表をテーブルに変換する

5145 ワード
PowerBI Excel PowerQuery Excel テキストリンク

twitterでバスの料金表を読み取るExcel問題を見かけたので、Power Queryでの変換クエリを考えてみました。
無駄に長い感があり、完敗です。
他の回答例など、コメントいただけるとうれしいです。
※2019/6/30追記：クエリ例その2を入れました。

料金表の例

例によって、下記のコードを詳細エディタに貼ると、テーブルになります。

元の料金表
let
    Source = "vZK7DsIwDEX/xXMH5+mmK59BGdqmmQpMmRD/jpsCSnhULHSxfBRF58bx/gK78xSPJwENtNEI27fRkqy5H0hwld60kaR16XTgSkJCdb8moZGID1JM5km6IFOQLYigUYjXKgtzitOUSTibIyPbWGvtlmyQW7GwYmHF71amNWuOahmQ934eRG3gVyEWwvVnvghz1IvfBvY76/ibFPapBpX6kPoBtkiSo1mCOUm8J0Z1/EeOwsdt+WuIHO3bxjgUnEyPiHNK28E2KXKkFEqPfl5gFRCuhxs=",
    Custom1 = Binary.Decompress(Binary.FromText(Source),Compression.Deflate),
    Custom2 = Table.FromRecords(Json.Document(Custom1))
in
    Custom2

料金表の様子

変換するクエリ：その1

当初思いついた案です。

コード

let
    Source = 元の料金表,
    #"Filled Down" = Table.FillDown(Source,Table.ColumnNames(Source)),
    #"Reversed Rows" = Table.ReverseRows(#"Filled Down"),
    Custom1 = Table.ToColumns(#"Reversed Rows")& 
              {{"発駅"} & List.RemoveFirstN(List.Reverse(Record.ToList(#"Reversed Rows"{0})),1) },
    Custom2 = Table.FromColumns(Custom1),
    #"Promoted Headers" = Table.PromoteHeaders(Custom2, [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"発駅"}, "着駅", "料金"),
    合成前 = Table.SelectRows(#"Unpivoted Other Columns", each Value.Type([料金]) = type number),
    Custom3 = List.Transform(Table.ToRows(合成前),each {_{1},_{0},_{2}}),
    Custom4 = Table.FromRows(
                    List.Distinct(Table.ToRows(合成前)& Custom3),
                    type table[発駅=text,着駅=text,料金=Int64.Type]
              )
in
    Custom4

処理途中の画像

Reversed Rows

Custom1

Custom2

Unpivoted Other Columns

完成図

変換するクエリ：その2

考え直した案です。大分すっきりしました。

コード

let
    Source = 元の料金表,
    各行をリストにしてNull除去 = List.Transform(Table.ToRows(Source),each List.RemoveNulls(_)),
    駅リスト = List.Transform( 各行をリストにしてNull除去,each _{0} ),
    片側データ =List.Combine(
                     List.Transform( 各行をリストにしてNull除去,each 
                        [発=_{0},
                         着= List.RemoveFirstN(駅リスト,List.PositionOf(駅リスト,発)+1),
                         料金=List.RemoveFirstN(_,1),
                         料金一覧 =List.Zip({List.Repeat({発},List.Count(着)),着,料金})
                        ][料金一覧]
                    )
               ),
    逆データ =List.Transform(片側データ,each {_{1},_{0},_{2}}),
    テーブル化　=Table.FromRows( 片側データ& 逆データ,type table[発=text,着=text,料金=Int64.Type] )
in
    テーブル化

解説

ポイントは、「片側データ」の箇所で、「着」のリストを得ることです。一例を挙げます。

駅リストの後ろに挿入する
    x=各行をリストにしてNull除去{2}{0},    　 //・・・"川内"が返って来ます
    y=List.PositionOf(駅リスト,x),　　　　 //・・・2が返ります。（0始まりで数える）
    z=List.RemoveFirstN(駅リスト,y+1),    //・・・川内より後の駅リストが返ります。

テストコードを本コードに挿入して試すと、川内より後の4駅が取れていることが分かります。