Power BI - 14 - Power Query SharePoint.Tables

全く関係ないですが PowerApps のプレビューについてリリースさましたね。とても楽しみです。
いち早く試してみたい場合には、

Microsoft PowerApps: power your business with apps

からリクエストしてみてください。そのうち案内が届くと思います。

さて、今年も参加させてもらっている Office 365 Advent Calendar 2015 の 2日目です。
Power BI  を推している今年なので、引き続き Power Query + Office 365 についてポストします。

Power Query は様々なソースからデータを収集加工(ETL)することができるとても便利な道具なのですが、SQL Server などデータベース以外を簡単にデータソースにすることができます。その中でOffice 365 に関連したソースとして SharePoint リストを取り上げてみます。
ja-jp なSharePoint サイトではうまく使えなかったりでした機能でしたが問題は概ね解決しているようです。


SharePoint リストをデータソースとするには コマンドから実行できます。

Office 365 ProPlus などエンタープライズ向けOffice の場合

クエリエディタを使用する場合は、

SharePoint.Tables(MSDN - SharePoint.Tables)
SharePoint.Tables(url as text, optional options as nullable record) as table

リファレンスがアップデートされていないのですが、引数 'option' の指定をしないとja-jp 環境上のSharePoint リストの取得に失敗しますので、

SharePoint.Tables("https://hogehoge.sharepoint.com", [ApiVersion = 15])

という式の投入が必要です。ApiVersion = "Auto" でもよいです。

サイト上のリスト一覧から目的のSharePoint リストを選択。他のリストも参照できますの管理で利用できるかもしれません。

目的のSharePoint リストのデータが取得できた場合、カスタムフィールド以外も既定で読み込まれます。必要なもののみを残すなどしてデータロードに移行すればよいのですが、システムフィールドが record となっていて、SharePoint アイテムに関連する情報も取得できている状態です。データ加工する工程で出力するフィールドに追加することもできるのですが、例えば、Author フィールドの値(型は record)にある Title フィールドはユーザ名なので、[Author][Title] と表現することができ、SharePoint アイテムに関連するユーザ名の取得できます。

let
    Source = SharePoint.Tables("https://hogehoge.sharepoint.com", [ApiVersion = 15]),
    SharePointリスト1 = Source{[Title="SharePointリスト1"]}[Items],
    AddColumn = Table.AddColumn(SharePointリスト1, "AuthorName", each [Author][Title]),
    DeleteColumns = Table.SelectColumns(AddColumn,{"Id", "Title", "Field1", "AuthorId", "AuthorName"})
in
    DeleteColumns

SharePoint アイテムに添付されたファイルについても同様です。
 [AttachmentFiles][ServerRelativeUrl] とすることで添付ファイルの Url を取得できるので Web.Contents 関数を組み合わせて使用しそれぞれのデータ抽出することも可能です。

let
    SiteUrl = "https://hogehoge.sharepoint.com",
    ListName = "SharePointListName",
    SharePointLists = SharePoint.Tables(SiteUrl, [ApiVersion = 15]),
    SeteteSharePointList = SharePointLists{[Title=ListName]}[Items],
    AttachmentEqTrue = Table.SelectRows(SeteteSharePointList, each ([Attachments] = true)),
    DeleteColumns = Table.SelectColumns(AttachmentEqTrue,{"AttachmentFiles"}),
    ExpandAttachmentFiles = Table.ExpandTableColumn(DeleteColumns, "AttachmentFiles", {"ServerRelativeUrl"}, {"RelativeUrl"}),
    AddColumn = Table.AddColumn(ExpandAttachmentFiles, "AttachmentFileUri", each Uri.Combine(SiteUrl,[RelativeUrl]))
in
    AddColumn

SharePoint.Files / SharePoint.Contents についてはおいおいポストしようかと。

[環境]
Excel 2016(FirstReleaseCurrent)：16.0.6228.1010 / 2.27.4163.551
Excel 2013 / Power Query for Excel：15.0.4771.1004 / 2.27.4163.242
Power BI Desktop：2.29.4217.221