Power Queryを使ってExcel上にRSS 2.0のフィードリーダーを作成する

気が付いたら顔面フリー素材になってました

というわけでExcel VBAでWebサービス - Excelでフィードを読もう（RSS2.0編） (1) フィードについて | マイナビニュースとか見かけたのでせっかくだしPower Queryで同じようなことをしてみたいと思ってやってみました
しかしPower Query先生は突然の職務放棄をするのであった…

まあとりあえずやっていきましょう
まずは拾ってくるフィードのURLが必要です
今回は我らがblogs.office.comのフィードを拾ってきます
blogs.office.com/feed/　　ですね

理由としては特にないのですがWordPressだとRSS 2.0で飛ばしてくれるので解析も楽だったりするというのが大きいです
ATOMは読む気力がなく…

(というよりblogs.msdn.comのフィードがRSS 2.0とATOMとが乱立しててうわあああってなったのでいったん放置)

WordPressのフィードなら改行とかもしてて綺麗なのでまずはこの辺から読むのがいいかなって

まあそんなことは置いといてとりあえずPower Queryに読み込みましょう
まずは適当に= Web.Contents("http://blogs.office.com/feed/")って書いときましょう
すると大体こんな感じになります

すると取得したデータに応じて勝手にルートオブジェクトまで読んでくれます
微妙に嫌な予感もしますが今回は特に問題ないのでこのまま行きます

channelの中身をとりあえず選択してRSSの基本的な情報を展開します
このときのitemがフィードのデータなのですが、そのままitem選択するとブログのタイトルがないのでこの時点で補完します

itemのTableの各要素に対し現在のtitleのデータを追加できればいいのでTable.AddColumnで対応します。このとき要素は一つですがTable.TransformColumnsで変換します
(もちろん関数で一つ目の要素にアクセスしてもいいのですが自力で作るのも手間)

適当に書くとTable.TransformColumns(ChangedType1, {"item", each Table.AddColumn(_, "Feed Title",(x) => ChangedType1{0}[title])})とかになります。
AddColumnの第3引数に値をそのまま入れたいのですがなんかうまくいかないので無駄な関数になっています

ここまでしたらあとは適当にitem選択して無駄な列消して並び替えるだけです
並び替えた後がこんな感じ

とりあえずクエリの全文はこんな感じ

let
    ソース = Xml.Tables(Web.Contents("http://blogs.office.com/feed/")),
    ChangedType = Table.TransformColumnTypes(ソース,{{"Attribute:version", type number}}),
    channel = ChangedType{0}[channel],
    ChangedType1 = Table.TransformColumnTypes(channel,{{"title", type text}, {"link", type text}, {"lastBuildDate", type datetime}, {"language", type text}, {"generator", type text}}),
    Custom1 = Table.TransformColumns(ChangedType1, {"item", each Table.AddColumn(_, "Feed Title",(x) => ChangedType1{0}[title])}),
    item = Custom1{0}[item],
    RemovedColumns = Table.RemoveColumns(item,{"comments", "http://purl.org/dc/elements/1.1/", "guid", "http://purl.org/rss/1.0/modules/content/", "http://wellformedweb.org/CommentAPI/", "http://purl.org/rss/1.0/modules/slash/", "enclosure"}),
    ReorderedColumns = Table.ReorderColumns(RemovedColumns,{"title", "Feed Title", "link", "pubDate", "description"})
in
    ReorderedColumns

まあこんなもんかなという感じなのでとりあえずワークシートに読み込みましょう

…そう、ここからが実は問題です
何がってPower Queryはテーブルを出力しますが各要素はすべて"文字列"で出してくれます
えっ？　RSSリーダーなのにURLに直接飛べない…？　ゴミだ…

何とかして作れたりしないん？ってなるんですがフォーラムにこんなものが
Transforming column content to a hyperlink in Power Queryによるとなるほど、直接Hyperlinkは生成できないから手で直接やってね！って書いてありますね

というわけで非常につらい選択ですが過去の遺産を用いましょう
イベントがどれに対応してるのかいまいち謎なのでこんな感じで対応

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer
    i = 2
    Do
        If Cells(i, 3).Value = "" Then
            Exit Sub
        End If
        Call Hyperlinks.Add(Cells(i, 3), Cells(i, 3).Value)
        i = i + 1
    Loop
End Sub

何かずれると一瞬で死ぬソースコードです
まあRSSリーダー専用ワークブックとしてはお仕事してくれると思います

とりあえずここまでやったので次のステップに行きましょう。
これだと1つのフィードを読むたびにシートが増殖していってあれなので複数のフィードを一つのシートにまとめて表示してみます

Tableを結合したりする謎関数があればいいんですが、複数のテーブルのフィールドが同じ可能性が少ないからでしょうがそのようなものはないので最初っからまとめて処理していきます
まず取得するフィードのListを作成します。こんな感じの
= {"http://blogs.office.com/feed/","http://grabacr.net/feed","http://blogs.msdn.com/b/shintak/rss.aspx"}
選んだフィードに特に悪意はございません。思いついたの選びました
フィードを増やしたり変更する場合最初のこのListを編集していきます

残りはさっきのクエリをベースとし、List.Transformで変更していきます。
さっきのと共通のletの最初の4行はこんな感じ

    feeds = {"http://blogs.office.com/feed/","http://grabacr.net/feed","http://blogs.msdn.com/b/shintak/rss.aspx"},
    xmls = List.Transform(feeds, each Xml.Tables(Web.Contents(_)){0}[channel]),
    liTrans = List.Transform(xmls, (x) => Table.TransformColumns(x, {"item", each Table.AddColumn(_, "Feed Title",(y) => x{0}[title])})),
    items = List.Transform(liTrans, each _{0}[item]),

わざわざ追加してくれたTransformColumnTypesは今回は必要ないので省略
そもそもさっきのも必要ないんですけどね。自動で追加されるのでまあしょうがない

こうしたらこの時点でテーブルの要素を必要な分だけにしておきます。
おそらく後からでも消せる気はするんですがまあどっちでも
List.Transform(items, each Table.RemoveColumns(_,{"comments", "http://purl.org/dc/elements/1.1/", "guid", "http://purl.org/rss/1.0/modules/content/", "http://wellformedweb.org/CommentAPI/", "http://purl.org/rss/1.0/modules/slash/", "enclosure", "category"},MissingField.Ignore)),
とかで対応できます。MissingField.Ignoreはないとエラーになるので消せませんが他は適宜増やしたり消したりで

とりあえず全部のTableの列が同じになったところで一つのTableにまとめます
TableをいったんListにして展開、その後Tableに戻します
Table→ListはおなじみList.TransformManyでやることで最初からずっといじってたListの要素に変換できます
そうしたらあとはTable.FromRecordsとかで簡単に

    merged = List.TransformMany(reorder, (x) => Table.ToRecords(x), (x,y) => y),
    tbTrans = Table.FromRecords(merged)

これでinにはtbTransでいけます
クエリ全文はこんな感じ

let
    feeds = {"http://blogs.office.com/feed/","http://grabacr.net/feed","http://blogs.msdn.com/b/shintak/rss.aspx"},
    xmls = List.Transform(feeds, each Xml.Tables(Web.Contents(_)){0}[channel]),
    liTrans = List.Transform(xmls, (x) => Table.TransformColumns(x, {"item", each Table.AddColumn(_, "Feed Title",(y) => x{0}[title])})),
    items = List.Transform(liTrans, each _{0}[item]),
    removed = List.Transform(items, each Table.RemoveColumns(_,{"comments", "http://purl.org/dc/elements/1.1/", "guid", "http://purl.org/rss/1.0/modules/content/", "http://wellformedweb.org/CommentAPI/", "http://purl.org/rss/1.0/modules/slash/", "enclosure", "category"},MissingField.Ignore)),
    reorder = List.Transform(removed, each Table.ReorderColumns(_, {"title","Feed Title", "link", "pubDate", "description"})),
    merged = List.TransformMany(reorder, (x) => Table.ToRecords(x), (x,y) => y),
    tbTrans = Table.FromRecords(merged)
in
    tbTrans

reorderのところは後からでもできます。あとからやるほうが楽です。

これでワークシートに読み込んでさっきのVBA使った結果がこんな感じです

案外使えそうな感じではありますね
あとはクエリを更新するだけで勝手にフィードが落ちてきてHyperlinkに変換してくれます