タグ別アーカイブ: PowerQuery

PowerQueryでRemember the milkからタスク一覧を抽出する (拡張)

先ほどのPowerQueryのクエリを拡張して他のカラムも取り込んで見ました。タグや期日なども取り込めるようにしてのでサンプルとしてより実用的になったと思います。

let
    Source = Web.Contents("< source uri >"),
    Document = Xml.Document(Source),
    Entries = Table.SelectRows(Document{0}[Value], each ([Name] = "entry")),
    Rows = Table.ToRows(Entries),
    ProjectedEntries = List.Transform(Rows, (x) => x{2}),
    P = List.Transform(ProjectedEntries, (x) => Table.SelectRows(x, each (([Name] = "id") or ([Name] = "updated") or ([Name] = "title") or ([Name] = "content")))),
    R = Table.FromList(List.Transform(P, (x) => Table.Range(Table.PromoteHeaders(Table.Transpose(x)),1,1)), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    S = Table.ExpandTableColumn(R, "Column1", {"updated", "id", "title", "content"}, {"updated", "id", "title", "content"}),
    SX = Table.AddColumn(S, "additional", each Table.PromoteHeaders(Table.Transpose(Table.FromRecords(List.Transform([content]{0}[Value][Value],(x) => [Name = Text.Trim(Text.Replace(x[Value]{0},":","")),Value = x[Value]{1}]))))),
    SY = Table.ExpandTableColumn(SX, "additional", {"Due", "Priority", "Time estimate", "Tags", "Location", "Postponed"}, {"additional.Due", "additional.Priority", "additional.Time estimate", "additional.Tags", "additional.Location", "additional.Postponed"}),
    S1 = Table.RenameColumns(SY,{{"updated", "更新日時"},{"title", "タスク"},{"id", "ID"},{"additional.Due", "期日"},{"additional.Priority", "優先度"},{"additional.Tags", "タグ"}}),
    S2 = Table.RemoveColumns(Table.ReorderColumns(S1,{"更新日時", "タスク", "期日", "優先度", "タグ", "ID"}),{"content","additional.Time estimate","additional.Location","additional.Postponed"})
    
in
    S2

PowerQueryでRemember the milkのRSSからタスク一覧を抽出する

PowerQueryでRemember the milkのフィードを加工してタスク一覧を抽出するクエリを書いてみました。データ加工のサンプルとして最適なので公開します。フィードのURIのみ変更してあります。

let
    Source = Web.Contents(< feed address >),
    Document = Xml.Document(Source),
    Entries = Table.SelectRows(Document{0}[Value], each ([Name] = "entry")),
    Rows = Table.ToRows(Entries),
    ProjectedEntries = List.Transform(Rows, (x) => x{2}),
    P = List.Transform(ProjectedEntries, (x) => Table.SelectRows(x, each (([Name] = "id") or ([Name] = "updated") or ([Name] = "title") or ([Name] = "content")))),
    R = Table.FromList(List.Transform(P, (x) => Table.Range(Table.PromoteHeaders(Table.Transpose(x)),1,1)), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    S = Table.ExpandTableColumn(R, "Column1", {"updated", "id", "title", "content"}, {"updated", "id", "title", "content"}),
    S0 = Table.AddColumn(S, "filtered content", each [content]),
    S1 = Table.RenameColumns(S,{{"updated", "更新日時"},{"title", "タスク"},{"id", "ID"}}),
    S2 = Table.RemoveColumns(Table.ReorderColumns(S1,{"更新日時", "タスク", "content", "ID"}),{"content"})
    
in
    S2

Excel PowerQuery/PowerPivotを利用する場合の推奨するタスクフロー

PowerQuery/PowerPivotは機能が多いため、いくつかの側面では機能重複を起こしている面がある。たとえば、PowerPivot単体でもデータのインポートが行える。また、PowerQueryもTable.Pivotを使って集計することが出来る。従って、一つのタスクを実行するにしても複数のフローが考えられる。しかし、現実的な分析を行うにはある程度、推奨できるタスクフローが定義できる。本稿では以下のタスクフローを推奨する。このフローはExcel 2013以降でPowerQuery、PowerPivotを使う場合を想定している。

  • Power Queryでデータを取得する
  • 分析に必要な加工をPower Queryで行う
  • Power Pivotにデータモデルを出力する
  • Power Pivotで集計を行い可視化する
  • このタスクフローではPower QueryをETLツールとして活用し、加工されたデータをPower Pivotで集計し可視化するというフレームワークをとっている。Power Pivotは内部的に列指向のデータベースを持っているため集計に適するが、一方でデータ加工を行うには式で定義できる制約があるためPower Queryには機能性・生産性で及ばない。従って、Power Pivotで集計のパフォーマンスを稼ぎ、データ加工はPower Queryで柔軟にというのがもっとも現実的と思われる。

    そのため、ETLツールとしてはPower Queryを活用する。また、本稿ではアドホックな分析を前提とするが、集計をある程度規格化する場合には、SQL Server Analytics Services (SSAS)で集計の仕方を配布することも検討してほしい。

    Excel PowerQueryでテーブルを結合する

    使っているExcelが2010以降でボリュームライセンスのExcelや単独のアプリケーションでの購入や企業用のOffice 365の購読の場合にはPowerBIに含まれるPowerQueryが使用できる。PowerQueryは多様な機能を持っているがテーブルの結合はデータ加工時には多用するため解説する。

    テーブル同士を結合するPowerQueryの関数はTable.Joinである。この関数は以下のような文法になっている。
    Table.Join(<テーブル1>,<結合に使用する列名1>,<テーブル2>,<結合に使用する列名2>[,<結合の方法>])
    結合の方法はJoinKind列挙型で以下のいずれかを取る。

    • TableKind.Inner
    • TableKind.LeftOuter
    • TableKind.RightOuter
    • TableKind.FullOuter
    • TableKind.LeftAnti
    • TableKind.RightAnti

    恐らく多用するのは内部結合 TableKind.Inner、外部結合 TableKind.LeftOuterの2つだろう。基本的にはSQLのJoinと変わらないがPowerQueryの場合には当然ながら各種のデータソースを混合して結合できるところにメリットがある。